Concatenate strings from different rows

Hello.

I have a table like:

Id Name Description OrderId
1 Microsoft This is a 0
1 Microsoft huge company. 1

I need to create a select query that will concatenate description for both entries and output it like

Microsoft - This is a huge company.

Try using the USE XML PATH () as part of the select statement; maybe something like:

Code Snippet

declare @.testData table
( id integer,
[Name] varchar(10),
Description varchar(15),
orderId integer
)
insert into @.testData
select 1, 'Microsoft', 'This is a', 0 union all
select 1, 'Microsoft', 'huge company.', 1

select distinct
[Name] + ' - ' +
( select description + ' ' as [text()]
from @.testData b
where a.id = b.id
order by orderId
for xml path ('')
) as outputString
from @.testData a

/*
outputString
--
Microsoft - This is a huge company.
*/

Concatenate Multiple Records Into One Field

Someone please help!. I am trying to create a view in SQL Server 2000 to use for a report but I am having problems with concatenating multiple values into one field. In my example below I am trying to list all records in my queried table in columnA then concatenate a list of all other records that share the same value in column B of the queried table into another field. If there are no other matches for a row in columnA then I would leave the corresponding field in columnB blank. Thanks in advance.

TABLE
ColumnA ColumnB
1......A
2......B
3......C
4......A
5......A
6......B
7......C
8......D
9......C
10.....E

EXPECTED OUTPUT
ColumnA ColumnB
1......4,5
2......6
3......7
4......1,5
5......1, 4
6......2
7......3
8......
9......3,7
10.....create function ConcatFld (@.RowId int, @.RowVal char(1))
returns varchar(100) AS
begin
declare @.Ret varchar(100)
set @.Ret=''
select @.Ret= @.Ret + cast(ColA as varchar)+',' from Tbl1 where ColB=@.RowVal and ColA<>@.RowId
if len(@.Ret) > 0
set @.Ret = left(@.Ret,len(@.Ret)-1)
return @.Ret
end
--------

select ColA, dbo.ConcatFld(ColA,ColB) from Tbl1|||Thanks Upalsen, this is exactly what I need!!!

Concatenate Dimension Attributes

Hi people,

This might be a really simple one hopefully, but is there a way I can create an attribute that is 2 other attributes concatenated, i.e.

Dim_Employee

EmployeeName

Employee Surname

Can I get EmployeeFullname by concatenating Name & Surname and if so how?

In SSAS2005 you create a new named calculation on the table in the datasource view.

Use TSQL EmployeeName + ',' + [Employee SureName]

In AS2000 you will have to write this TSQL in the name column for the dimension level. You do this in the dimension editor.

HTH

Thomas Ivarsson

|||

Cheers.

Easy when you know how :)

ConCat data

I need to create a csv file where it's just one giant file.
There is only one field I pull from a table called USerID(Char8). Then for
every record in the table create a file like this.
Receipents-c/n=XXXXX%Receipents-c/n=XXXXXReceipents-c/n=XXXXXReceipents-c/n=
XXXXX This will then Import into Exchange for a Distrubution List. Any IdeasSee if this link gives you some ideas
http://www.rac4sql.net/xp_execresultset.asp
Anith|||Would that not put each order on a separate line.
I need it all strung together ..as one big file...
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23QJSioLBFHA.936@.TK2MSFTNGP12.phx.gbl...
> See if this link gives you some ideas
> http://www.rac4sql.net/xp_execresultset.asp
> --
> Anith
>|||Anith has pointed you to a trick to create a file for each record/row for
your table.
Look like you want to concatenate the rows into a single string? If so, it's
probably best to do it from the client side (i.e. vb/script/etc).
Though, I am bit about your comment regarding CSV in your first
post. Perhaps, you want to clarify so we can help.
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:L92dnbdsqJ2N6WTcRVn-gg@.kconline.com...
> Would that not put each order on a separate line.
> I need it all strung together ..as one big file...
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23QJSioLBFHA.936@.TK2MSFTNGP12.phx.gbl...
>|||sorry .. I just meant to have a csv extension to the filename.
do you have an example vbscript to concat these records from the sql table
?
thanks again.
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23C0Ko0MBFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Anith has pointed you to a trick to create a file for each record/row for
> your table.
> Look like you want to concatenate the rows into a single string? If so,
> it's probably best to do it from the client side (i.e. vb/script/etc).
> Though, I am bit about your comment regarding CSV in your first
> post. Perhaps, you want to clarify so we can help.
> --
> -oj
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:L92dnbdsqJ2N6WTcRVn-gg@.kconline.com...
>|||Here is a vbscript.
Main()
Sub Main()
Dim sqlcnt,rs,s
s="Begin"
Set cntsql = CreateObject("ADODB.Connection")
With cntsql
.provider = "SQLOLEDB"
.connectionstring = "Data Source=.\dev;integrated security=SSPI"
.Open
Set rs = .Execute("select OrderID from Northwind..Orders")
Do Until rs.EOF
s = s & rs.Fields("OrderID") & ","
rs.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set cntsql = Nothing
s = s & "End"
Call WriteToFile(s)
End Sub
Function WriteToFile(s)
Dim fso, tf
Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile("c:\test.csv", True)
tf.Write(s)
tf.Close()
Set fso= Nothing
Set tf= Nothing
End Function
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:ZLydnddhH-JSVWTcRVn-tw@.kconline.com...
> sorry .. I just meant to have a csv extension to the filename.
> do you have an example vbscript to concat these records from the sql table
> ?
> thanks again.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23C0Ko0MBFHA.3492@.TK2MSFTNGP12.phx.gbl...
>|||Thanks again.
"oj" <nospam_ojngo@.home.com> wrote in message
news:uyuNXkQBFHA.3368@.TK2MSFTNGP10.phx.gbl...
> Here is a vbscript.
> Main()
> Sub Main()
> Dim sqlcnt,rs,s
> s="Begin"
> Set cntsql = CreateObject("ADODB.Connection")
> With cntsql
> .provider = "SQLOLEDB"
> .connectionstring = "Data Source=.\dev;integrated security=SSPI"
> .Open
> Set rs = .Execute("select OrderID from Northwind..Orders")
> Do Until rs.EOF
> s = s & rs.Fields("OrderID") & ","
> rs.MoveNext
> Loop
> .Close
> End With
> Set rs = Nothing
> Set cntsql = Nothing
> s = s & "End"
> Call WriteToFile(s)
> End Sub
> Function WriteToFile(s)
> Dim fso, tf
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set tf = fso.CreateTextFile("c:\test.csv", True)
> tf.Write(s)
> tf.Close()
> Set fso= Nothing
> Set tf= Nothing
> End Function
>
> --
> -oj
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:ZLydnddhH-JSVWTcRVn-tw@.kconline.com...
>|||oj The script worked great but...
The problem I'm having it puts an extra %Recipients/cn= at the end of the
file. The Import process that is using this output fails on this bogus
record since it doesn't have an ID attached. How can I remove this last
record from the file if it doesn't have a valid record.
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:msKdnbiYuoaxv2fcRVn-vw@.kconline.com...
> Thanks again.
>
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uyuNXkQBFHA.3368@.TK2MSFTNGP10.phx.gbl...
>|||You would need to check the returned value before concatenating it in your
vbscript.
e.g.
if rs("your_keycol")="abc" then
'it is good and concatenate
else
'it is bad and ignore
endif
Take a look at this site for help on vbscripting
http://msdn.microsoft.com/library/e...me=true

-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:g5WdnagRsbodGpzfRVn-2A@.kconline.com...
> oj The script worked great but...
> The problem I'm having it puts an extra %Recipients/cn= at the end of the
> file. The Import process that is using this output fails on this bogus
> record since it doesn't have an ID attached. How can I remove this last
> record from the file if it doesn't have a valid record.
>
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:msKdnbiYuoaxv2fcRVn-vw@.kconline.com...
>

concat all col2 values for each col1, and add sum(col3) (was "query help")

Hi,
Can anybody help me to create a single query? I have this problem.

CREATE TABLE t1 (
col1 VARCHAR(100)
, col2 VARCHAR(100)
, col3 INT)

INSERT INTO t1 VALUES('A001','Tom',30)
INSERT INTO t1 VALUES('A001','Rick',40)
INSERT INTO t1 VALUES('A001','Harry',10)

INSERT INTO t1 VALUES('A002','Peter',50)
INSERT INTO t1 VALUES('A002','Sam',50)

INSERT INTO t1 VALUES('A003','Fred',50)

I want a resultset like this ...
i.e col1 col2(all the values would be represented in a single row for each col1) and sum(col3)

(Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)

A001 Tom Rick Harry 80 --sum(col3)
A002 Peter Sam NULL 100
A003 Fred NULL NULL 50

Any help would be greatly appreciated !!(Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)
Based on this the below works. I think it is about as efficient as it can be though verbose for the sake of transparency :)

SET NOCOUNT ON
CREATE TABLE t1 (
col1 VARCHAR(100)
, col2 VARCHAR(100)
, col3 INT)

INSERT INTO t1 VALUES('A001','Tom',30)
INSERT INTO t1 VALUES('A001','Rick',40)
INSERT INTO t1 VALUES('A001','Harry',10)
INSERT INTO t1 VALUES('A002','Peter',50)
INSERT INTO t1 VALUES('A002','Sam',50)
INSERT INTO t1 VALUES('A003','Fred',50)

SELECT Col1,
Col2a,
Col2b,
Col2c,
SUM(Col3) AS TheTotal
FROM --Pivot data
(SELECT TOP 100 PERCENT
Col1,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
ORDER BY
B.Col2) AS Col2a,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
AND B.Col2 NOT IN (SELECT TOP 1 Col2
FROM dbo.t1 AS C
WHERE C.Col1 = A.Col1
ORDER BY
C.Col2)
ORDER BY
B.Col2) AS Col2b,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
AND B.Col2 NOT IN (SELECT TOP 2 Col2
FROM dbo.t1 AS C
WHERE C.Col1 = A.Col1
ORDER BY
C.Col2)) AS Col2c,
Col3
FROM dbo.t1 AS A
ORDER BY
Col1,
Col2) AS DerT
GROUP BY
Col1,
Col2a,
Col2b,
Col2c

DROP TABLE t1

SET NOCOUNT OFF
HTH|||Based on this the below works. I think it is about as efficient as it can be though verbose for the sake of transparency :)

Awesome, as usual...thanks a ton Pootie!!:rolleyes:|||nevermind, you have a better solution above.|||As a possible alternative, blindman's neat function here could be adapted, resulting in a much simpler query:

SELECT col1, dbo.Concat_ICD(col1) as TheNames, Sum(col3) as TheTotal
FROM t1
GROUP BY col1

http://www.dbforums.com/showthread.php?t=1605725

This would not produce the visible NULL in the result, but I was presuming that wasn't a requirement.|||The function is your best solutions, because it works for any number of records.

By the way, I wish I could take credit for that function, but it is actually one of the many things I have learned from participating in this forum over that past few years.|||Actually that was the solution I hoped to use - it allows n values to be concatenated. However I read the requirement as the return putting the names into three columns rather than one. If this isn't a requirement then defo go with Blindman's solution.|||I wanted it in three different columns.So I used Pootie's one.
Anyways,Thanks everybody for their valuable info.|||for comparison purposes, here is the equivalent query in mysql --select col1
, group_concat(col2)
, sum(col3)
from daTable
group
by col1:)|||I'm calling you on that one Rudy. Did you read the requirements carefully?|||...but here is a shorter method of coding it for SQL Server:SET NOCOUNT ON
CREATE TABLE #t1
(col1 VARCHAR(100),
col2 VARCHAR(100),
col3 INT)

INSERT INTO #t1 VALUES('A001','Tom',30)
INSERT INTO #t1 VALUES('A001','Rick',40)
INSERT INTO #t1 VALUES('A001','Harry',10)
INSERT INTO #t1 VALUES('A002','Peter',50)
INSERT INTO #t1 VALUES('A002','Sam',50)
INSERT INTO #t1 VALUES('A003','Fred',50)

select A.col1,
min(A.col2) as name1,
min(B.col2) as name2,
min(C.col2) as name3,
max(coalesce(A.col3, 0) + coalesce(B.col3, 0) + coalesce(C.col3, 0)) as col3total
from #t1 A
left outer join #t1 B on A.col1 = B.col1 and A.col2 < B.col2
left outer join #t1 C on B.col1 = C.col1 and B.col2 < C.col2
group by A.col1

drop table #t1|||I'm calling you on that one Rudy. Did you read the requirements carefully?
oh, SHEEEEEEEESH, okay :S
select col1
, group_concat(col2 separator ' ')
, sum(col3)
from t1
group
by col1|||for comparison purposes, here is the equivalent query in mysql --select col1
, group_concat(col2)
, sum(col3)
from daTable
group
by col1:)

...and that would be just great if it was a real ANSI compliant databa...

Oh, never mind|||pot? meet kettle

kettle? meet pot

:p|||That was great,thank you Blindman.And thank you all of you for your help.

Computing change from first sales by employee

I'll use AdventureWorks to frame my question, as then I can extend any suggestions to the possible applications I need.

I want to create a calculation which finds the change of each employee's monthly sales amount from their first month's sales.

e.g. for each month it will show how their sales for that month differs from the first month they ever made a sale.

What I'm struggling with is that one employee may have made their first sale in 2001, while another made their first sale in 20003. I'd like to be able to show how each employee's sales change relative to their first month, i.e. in month 2, month 3, etc.

I am thinking that I will need to define a new calculated measure which is the number of months elapsed for each employee since their first sale and then define a calculation for their deltas per month, but I'm not sure.

I've read up on OpeningPeriod() and the "Opening Period Balance" template, but I'm stuck. Using AS 2005.

Thanks for any suggestions.

-Leif Kirschenbaum

Leif,

Here is an example that I think will give you what you are looking for. I included a measure called "First Months Sales" which is just to show how the calculation is working and is not really needed for the end result.

HTH,

Steve

WITH

MEMBER [Date].[Calendar].[First Month With Sales]

AS

Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0)

MEMBER [Measures].[First Months Sales]

AS

([Date].[Calendar].[First Month With Sales],

[Measures].[Reseller Sales Amount]),

FORMAT_STRING = "currency"

MEMBER [Measures].[Difference]

AS

IIF(Exists({[Date].[Calendar].CurrentMember},{Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0).Lead(1):NULL}).Count = 1 AND

[Measures].[Reseller Sales Amount] > 0,

[Measures].[Reseller Sales Amount] - ([Date].[Calendar].[First Month With Sales],[Measures].[Reseller Sales Amount]),

NULL),

FORMAT_STRING = "currency"

SELECT

{[Date].[Calendar].[Month].Members} ON COLUMNS,

{[Reseller].[Reseller].[Bike Rims Company],

[Reseller].[Reseller].[Certified Sports Supply]} *

{[Measures].[Reseller Sales Amount],

[Measures].[First Months Sales],

[Measures].[Difference]} ON ROWS

FROM

[Adventure Works]

|||Great!
Thanks.
I do need one other thing, I need a dimension which counts the months from the first month for each employee. Right now in the browser when I drag Employee hierarchy to the rows field and Calendar to the columns field the difference for each employee starts in a different month. It would be useful to be able to drag "Months of Employment" to the columns field.
Would I do:

CREATE MEMBER CURRENTCUBE.[Date].[Calendar].[Months of Employment]
AS
[Date].[Calendar].[Month] - [Date].[Calendar].[First Month With Sales],
FORMAT_STRING = "Standard",
NON_EMPTY_BEHAVIOR = { [Reseller Sales-Sales Amount] },
VISIBLE = 1 ;

That doesn't work.

CREATE MEMBER CURRENTCUBE.[Measures].[Months of Employment]

AS

[Date].[Calendar].[Month] - [Date].[Calendar].[First Month With Sales],

FORMAT_STRING = "Standard",

NON_EMPTY_BEHAVIOR = { [Reseller Sales-Sales Amount] },

VISIBLE = 1 ;

also doesn't work, as I can't drag "Months of Employment" to the columns field.|||

Leif,

Try the following:

MEMBER [Measures].[Months of Employment]

AS

IIF(Exists({[Date].[Calendar].CurrentMember},{Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0).Lead(1):NULL}).Count = 1,

{Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0):[Date].[Calendar].CurrentMember}.Count - 1,

NULL),

FORMAT_STRING = "#,#"

Computer Hang Up when using MDX Query Builder?

hello,
I have some reports using OLAP cube as data sources. When I use mdx
query builder to create any mdx statement as dataset, my computer will hang
up and pop up a window says:
Preparing Query: The query preparation is in process, to cancel, press
CTRL+C for at least half a second.
Even if I press CTRL+C for a long time, my computer still has no
response and hang up forever. I have tried these mdx statement in my SQL
server and they work fine. So this should not be an Analysis Serverices
problem. Would you please tell me how to solve this problem?I've found that if I create a huge data set with the mdx query builder, I
have to leave my computer to it and go get a cup of coffee while it chews
through the data.
Or you could add a few filters to it before starting the query execution, to
make sure the data set it returns is too big. Adding a date filter and make
it default to the last month or date will usually decrease the amount of
data returned.
My pc is running with 2 GB RAM and not a lot of applications in the back
ground. It still uses some time to chew through a data set that finishes
quite quickly in the SQL Server management studio, so I agree with you, it's
a RS issue more than an AS issue. You just have to be carefull about your
statement while using the builer, I guess.
Kaisa M. Lindahl Lervik
"jimmy" <jimmy@.discussions.microsoft.com> wrote in message
news:7375D569-D9C8-4309-8BA6-E46C8582FE32@.microsoft.com...
> hello,
> I have some reports using OLAP cube as data sources. When I use mdx
> query builder to create any mdx statement as dataset, my computer will
> hang
> up and pop up a window says:
> Preparing Query: The query preparation is in process, to cancel, press
> CTRL+C for at least half a second.
> Even if I press CTRL+C for a long time, my computer still has no
> response and hang up forever. I have tried these mdx statement in my SQL
> server and they work fine. So this should not be an Analysis Serverices
> problem. Would you please tell me how to solve this problem?
>|||write more efficient MDX statements?
share your MDX statement when you're having performance problems?
try not to use soo many crossjoins?
-Aaron
Kaisa M. Lindahl Lervik wrote:
> I've found that if I create a huge data set with the mdx query builder, I
> have to leave my computer to it and go get a cup of coffee while it chews
> through the data.
> Or you could add a few filters to it before starting the query execution, to
> make sure the data set it returns is too big. Adding a date filter and make
> it default to the last month or date will usually decrease the amount of
> data returned.
> My pc is running with 2 GB RAM and not a lot of applications in the back
> ground. It still uses some time to chew through a data set that finishes
> quite quickly in the SQL Server management studio, so I agree with you, it's
> a RS issue more than an AS issue. You just have to be carefull about your
> statement while using the builer, I guess.
> Kaisa M. Lindahl Lervik
>
> "jimmy" <jimmy@.discussions.microsoft.com> wrote in message
> news:7375D569-D9C8-4309-8BA6-E46C8582FE32@.microsoft.com...
> > hello,
> >
> > I have some reports using OLAP cube as data sources. When I use mdx
> > query builder to create any mdx statement as dataset, my computer will
> > hang
> > up and pop up a window says:
> >
> > Preparing Query: The query preparation is in process, to cancel, press
> > CTRL+C for at least half a second.
> >
> > Even if I press CTRL+C for a long time, my computer still has no
> > response and hang up forever. I have tried these mdx statement in my SQL
> > server and they work fine. So this should not be an Analysis Serverices
> > problem. Would you please tell me how to solve this problem?
> >
> >|||Hi, Kaisa
Thanks a lot for your suggestions. I have tried a very simple mdx with a
middle size data set. e.g.
SELECT NON EMPTY { } ON COLUMNS,
{ ([Financial Period].[Fiscal].[Fiscal Year].ALLMEMBERS ) } ON ROWS FROM
[myCube]
My computer has a 1GB RAM, not so bad...This query will take about 1
second in my SQL server, however in ES it just hang up for hours and never
get back again, much more than a cup of coffee time.
I will try that on another computer to have a test and I do not think
that will happen too.
Do you think I should remove SQL server or Visual Studio and install
again?Thanks.
"Kaisa M. Lindahl Lervik" wrote:
> I've found that if I create a huge data set with the mdx query builder, I
> have to leave my computer to it and go get a cup of coffee while it chews
> through the data.
> Or you could add a few filters to it before starting the query execution, to
> make sure the data set it returns is too big. Adding a date filter and make
> it default to the last month or date will usually decrease the amount of
> data returned.
> My pc is running with 2 GB RAM and not a lot of applications in the back
> ground. It still uses some time to chew through a data set that finishes
> quite quickly in the SQL Server management studio, so I agree with you, it's
> a RS issue more than an AS issue. You just have to be carefull about your
> statement while using the builer, I guess.
> Kaisa M. Lindahl Lervik
>
> "jimmy" <jimmy@.discussions.microsoft.com> wrote in message
> news:7375D569-D9C8-4309-8BA6-E46C8582FE32@.microsoft.com...
> > hello,
> >
> > I have some reports using OLAP cube as data sources. When I use mdx
> > query builder to create any mdx statement as dataset, my computer will
> > hang
> > up and pop up a window says:
> >
> > Preparing Query: The query preparation is in process, to cancel, press
> > CTRL+C for at least half a second.
> >
> > Even if I press CTRL+C for a long time, my computer still has no
> > response and hang up forever. I have tried these mdx statement in my SQL
> > server and they work fine. So this should not be an Analysis Serverices
> > problem. Would you please tell me how to solve this problem?
> >
> >
>
>|||im not sure that's such a simple MDX statement
do you have granularity to the seconds dimension?
how many members do you have in this dim?
I would do this and see if it's a lot faster:
Select [Financial Period].[Fiscal].[Fiscal Year].MEMBERS on COLUMNS
FROM MyCube
and see if that's a lot faster.
-Aaron
jimmy wrote:
> Hi, Kaisa
> Thanks a lot for your suggestions. I have tried a very simple mdx with a
> middle size data set. e.g.
> SELECT NON EMPTY { } ON COLUMNS,
> { ([Financial Period].[Fiscal].[Fiscal Year].ALLMEMBERS ) } ON ROWS FROM
> [myCube]
> My computer has a 1GB RAM, not so bad...This query will take about 1
> second in my SQL server, however in ES it just hang up for hours and never
> get back again, much more than a cup of coffee time.
> I will try that on another computer to have a test and I do not think
> that will happen too.
> Do you think I should remove SQL server or Visual Studio and install
> again?Thanks.
>
> "Kaisa M. Lindahl Lervik" wrote:
> > I've found that if I create a huge data set with the mdx query builder, I
> > have to leave my computer to it and go get a cup of coffee while it chews
> > through the data.
> > Or you could add a few filters to it before starting the query execution, to
> > make sure the data set it returns is too big. Adding a date filter and make
> > it default to the last month or date will usually decrease the amount of
> > data returned.
> > My pc is running with 2 GB RAM and not a lot of applications in the back
> > ground. It still uses some time to chew through a data set that finishes
> > quite quickly in the SQL Server management studio, so I agree with you, it's
> > a RS issue more than an AS issue. You just have to be carefull about your
> > statement while using the builer, I guess.
> >
> > Kaisa M. Lindahl Lervik
> >
> >
> > "jimmy" <jimmy@.discussions.microsoft.com> wrote in message
> > news:7375D569-D9C8-4309-8BA6-E46C8582FE32@.microsoft.com...
> > > hello,
> > >
> > > I have some reports using OLAP cube as data sources. When I use mdx
> > > query builder to create any mdx statement as dataset, my computer will
> > > hang
> > > up and pop up a window says:
> > >
> > > Preparing Query: The query preparation is in process, to cancel, press
> > > CTRL+C for at least half a second.
> > >
> > > Even if I press CTRL+C for a long time, my computer still has no
> > > response and hang up forever. I have tried these mdx statement in my SQL
> > > server and they work fine. So this should not be an Analysis Serverices
> > > problem. Would you please tell me how to solve this problem?
> > >
> > >
> >
> >
> >

Computed 'Days to go' Column

Given a column of dates, I would like to create a computed column showing
how many days from the current date until that date (ignoring the year) next
occurs.
E.G. given 3 rows:
DateID StartDate
1 2 January 1954
2 1 March 1978
3 30 December 2001
if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 59
3 31 December 2001 364
and on 1st Jan 2008 (leap year):
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 60
3 31 December 2001 365
I have a stored procedure that does the calculation correctly (I think ;),
however it requires parameters, and I need a computed column or view. ANy
help much appreciated
TIA,
Paul Bryant
===================
ALTER PROCEDURE DaysToGo
@.DateID int
AS
DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
SET @.OldDay =
(SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SET @.OldMonth =
(SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
AS NVARCHAR)
IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
ELSE
SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
================================================== =========
You could use a calendar table for this... just populate it as far out as
you need, then you can add DATEADD(YEAR, YEAR(GETDATE()), '19540102') to get
January 2, 1954, then take the datediff in days between today and that date.
To see how to populate the calendar table:
http://www.aspfaq.com/2519
If you decide to go that route, we can help you with more specific code.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
"Paul Bryant" <paul@.gap66.com> wrote in message
news:%23x%23t0jlREHA.2520@.TK2MSFTNGP11.phx.gbl...
> Given a column of dates, I would like to create a computed column showing
> how many days from the current date until that date (ignoring the year)
> next
> occurs.
> E.G. given 3 rows:
> DateID StartDate
> 1 2 January 1954
> 2 1 March 1978
> 3 30 December 2001
> if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 59
> 3 31 December 2001 364
> and on 1st Jan 2008 (leap year):
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 60
> 3 31 December 2001 365
> I have a stored procedure that does the calculation correctly (I think ;),
> however it requires parameters, and I need a computed column or view. ANy
> help much appreciated
> TIA,
> Paul Bryant
> ===================
> ALTER PROCEDURE DaysToGo
> @.DateID int
> AS
> DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
> SET @.OldDay =
> (SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SET @.OldMonth =
> (SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
> AS NVARCHAR)
> IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
> SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
> ELSE
> SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
>
> ================================================== =========
>
|||BTW and FWIW, I added this exact example to the article.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
sqlsql

Computed 'Days to go' Column

Given a column of dates, I would like to create a computed column showing
how many days from the current date until that date (ignoring the year) next
occurs.
E.G. given 3 rows:
DateID StartDate
1 2 January 1954
2 1 March 1978
3 30 December 2001
if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 59
3 31 December 2001 364
and on 1st Jan 2008 (leap year):
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 60
3 31 December 2001 365
I have a stored procedure that does the calculation correctly (I think ;),
however it requires parameters, and I need a computed column or view. ANy
help much appreciated
TIA,
Paul Bryant
=================== ALTER PROCEDURE DaysToGo
@.DateID int
AS
DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
SET @.OldDay =
(SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SET @.OldMonth =
(SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
AS NVARCHAR)
IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
ELSE
SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
===========================================================You could use a calendar table for this... just populate it as far out as
you need, then you can add DATEADD(YEAR, YEAR(GETDATE()), '19540102') to get
January 2, 1954, then take the datediff in days between today and that date.
To see how to populate the calendar table:
http://www.aspfaq.com/2519
If you decide to go that route, we can help you with more specific code.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
"Paul Bryant" <paul@.gap66.com> wrote in message
news:%23x%23t0jlREHA.2520@.TK2MSFTNGP11.phx.gbl...
> Given a column of dates, I would like to create a computed column showing
> how many days from the current date until that date (ignoring the year)
> next
> occurs.
> E.G. given 3 rows:
> DateID StartDate
> 1 2 January 1954
> 2 1 March 1978
> 3 30 December 2001
> if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 59
> 3 31 December 2001 364
> and on 1st Jan 2008 (leap year):
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 60
> 3 31 December 2001 365
> I have a stored procedure that does the calculation correctly (I think ;),
> however it requires parameters, and I need a computed column or view. ANy
> help much appreciated
> TIA,
> Paul Bryant
> ===================> ALTER PROCEDURE DaysToGo
> @.DateID int
> AS
> DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
> SET @.OldDay => (SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SET @.OldMonth => (SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
> AS NVARCHAR)
> IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
> SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
> ELSE
> SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
>
> ===========================================================>|||BTW and FWIW, I added this exact example to the article.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)

computed columns and casting

Why does the following fail the parser ?
CREATE PROC [dbo].[GetQuestionAnswers]
@.QuestionID int
AS
DECLARE @.TotalCount int
SELECT @.TotalCount = Sum(AnswerCount)
FROM Answer
WHERE QuestionID = @.QuestionID
IF (@.TotalCount = 0)
BEGIN
SET @.TotalCount = 1
END
SELECT AnswerID, AnswerText, CAST(AnswerCount / @.TotalCount AS double) As
AnswerFraction
FROM Answer
WHERE QuestionID = @.QuestionID
ORDER BY Rank
GODouble is not a Transact-SQL system data type. Use float or real for
approximate values or decimal for exact numeric values.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:ecBae3oLFHA.4028@.tk2msftngp13.phx.gbl...
> Why does the following fail the parser ?
> CREATE PROC [dbo].[GetQuestionAnswers]
> @.QuestionID int
> AS
> DECLARE @.TotalCount int
> SELECT @.TotalCount = Sum(AnswerCount)
> FROM Answer
> WHERE QuestionID = @.QuestionID
> IF (@.TotalCount = 0)
> BEGIN
> SET @.TotalCount = 1
> END
> SELECT AnswerID, AnswerText, CAST(AnswerCount / @.TotalCount AS double) As
> AnswerFraction
> FROM Answer
> WHERE QuestionID = @.QuestionID
> ORDER BY Rank
> GO
>|||Hi Dan, and thanks for the response.
I should have phrased my question differently (because I quickly found out
that Double is not SQLDataType).
How to write SQL that creates a float or real or decimal psuedocolumn equal
to the quotient of two integer values (columns of integer type, or
otherwise) -- I do not want truncation to the nearest integer.
Thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23gnckLpLFHA.1308@.tk2msftngp13.phx.gbl...
> Double is not a Transact-SQL system data type. Use float or real for
> approximate values or decimal for exact numeric values.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:ecBae3oLFHA.4028@.tk2msftngp13.phx.gbl...
>|||Use
CAST(AnswerCount / @.TotalCount AS Decimal(12,2))
Madhivanan|||John,
It will not work to cast after dividing. You need to cast or
force implicit conversion before dividing.
Won't work:
cast(AnswerCount / @.TotalCount as double precision)
Either of these will work:
cast(AnswerCount as double precision) / @.TotalCount
(1e0*AnswerCount)/@.TotalCount
Steve Kass
Drew University
John A Grandy wrote:

>Hi Dan, and thanks for the response.
>I should have phrased my question differently (because I quickly found out
>that Double is not SQLDataType).
>How to write SQL that creates a float or real or decimal psuedocolumn equal
>to the quotient of two integer values (columns of integer type, or
>otherwise) -- I do not want truncation to the nearest integer.
>Thanks.
>"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>news:%23gnckLpLFHA.1308@.tk2msftngp13.phx.gbl...
>
>
>|||CREATE PROCEDURE GetQuestionAnswers ( @.my_question_id INTEGER)
AS
SELECT A1.answer_id, A1.answer_txt,
(A1.answer_count
/ (SELECT CASE WHEN SUM(A2.answer_count) = 0
THEN CAST (1.00 AS REAL)
ELSE SUM(A2.answer_count) END)
FROM Answers AS A2
WHERE A2.question_id = A1.question_id))
AS answer_fraction
FROM Answers AS A1
WHERE A1.question_id = @.my_question_id;
Untested; the CASE expression will resolve to the highest data type in
the THEN or ELSE clauses. This saves a local variable and lets the
optimizer do its thing.

Computed Column - Error Validating Formula

I want to create a computed column with this formula:

ISNULL(NULLIF (tot_mnc, 0) / NULLIF (repl_value, 0), 0)

It works in a straight select query, but when I put it in the formula of the table design window, I get an error "Error validating the formula for column 'test_fci'"

I don't know if it's relevant but repl_value is itself a computed column with the formula:

(repl_value_e_g + repl_value_aux)

Is it possible to use the system functions in a computed column? If not, how would I pass those values into a udf and use it for the formula?

Thanks.

You are correct: you are not allowed to create a computed column that is based on a different computed column. You can use the built-in functions, so just create your new computed column based on the calculations from the base columns.

If you were to run an ALTER statement to create your computed column based on a computed column you would potentially get an error message like:

Msg 1759, Level 16, State 0, Line xxxx

Computed column 'ccccc' in table 'tttttt' is not allowed to be used in another computed-column definition.

Kent

|||

That was it, thank you. The table designer gui hid the details of the error message and I didn't know which of several possiblities were causing the error.

Thanks again.

Compound Primary Key - order not as expected

Hello,

if you create this table:

create table hello (
int a
, int b
constraint pk_hello primary key clustered ( a, b )
)

and then insert the following records

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

and then do

select a,b from hello

the output seems to be:

a,b
1,1
2,1
3,1
1,2
2,2
3,2
1,3
2,3
3,3

which is wrong and (i think) is reflecting the actual index order
and physical order on disk

it should be:

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

i have tested this on a table with 500,000 records

and sure enough if you declare the clustered primary key fields in
reverse order:

constraint pk_hello primary key clustered ( b, a )

two things happen:

- the select with no order by returns the records in the expected order
- queries relying on that order run MUCH FASTER

has anyone else seen / noticed this?John Rivers wrote:
> Hello,
> if you create this table:
> create table hello (
> int a
> , int b
> constraint pk_hello primary key clustered ( a, b )
> )
> and then insert the following records
> a,b
> 1,1
> 1,2
> 1,3
> 2,1
> 2,2
> 2,3
> 3,1
> 3,2
> 3,3
> and then do
> select a,b from hello
> the output seems to be:
> a,b
> 1,1
> 2,1
> 3,1
> 1,2
> 2,2
> 3,2
> 1,3
> 2,3
> 3,3
> which is wrong and (i think) is reflecting the actual index order
> and physical order on disk

This is not wrong at all. As long as you do not have an "ORDER BY"
clause the RDBMS is free to return records in *any* order.

> it should be:
> a,b
> 1,1
> 1,2
> 1,3
> 2,1
> 2,2
> 2,3
> 3,1
> 3,2
> 3,3
> i have tested this on a table with 500,000 records
> and sure enough if you declare the clustered primary key fields in
> reverse order:
> constraint pk_hello primary key clustered ( b, a )
> two things happen:
> - the select with no order by returns the records in the expected order

Again: you have to adjust your expectations.

> - queries relying on that order run MUCH FASTER
> has anyone else seen / noticed this?

Yes.

Cheers

robert|||Order is not guaranteed unless you include an ORDER BY. This is by design.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"John Rivers" <first10@.btinternet.com> wrote in message
news:1146048739.469710.138210@.e56g2000cwe.googlegr oups.com...
Hello,

if you create this table:

create table hello (
int a
, int b
constraint pk_hello primary key clustered ( a, b )
)

and then insert the following records

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

and then do

select a,b from hello

the output seems to be:

a,b
1,1
2,1
3,1
1,2
2,2
3,2
1,3
2,3
3,3

which is wrong and (i think) is reflecting the actual index order
and physical order on disk

it should be:

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

i have tested this on a table with 500,000 records

and sure enough if you declare the clustered primary key fields in
reverse order:

constraint pk_hello primary key clustered ( b, a )

two things happen:

- the select with no order by returns the records in the expected order
- queries relying on that order run MUCH FASTER

has anyone else seen / noticed this?|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.

You need to read a book on RDBMS; you are still locked into a file
system mind set.|||Hello,

when a clustered index is present the records *are* physically ordered
on disk to match the index

that is the whole point of a clustered index

and by default a select statement with no ORDER BY will always return
data in the order of the clustered index (when present)

this can easily be proved by watching the Execution Plan

the issue i am trying to highlight concerns the order of the records on
disk when a *compound* clustered index is present

i have seen cases when it is not as expected

maybe you can enjoy reading that RDBMS book :-)

best wishes,

john|||John Rivers wrote:
> Hello,
> when a clustered index is present the records *are* physically ordered
> on disk to match the index
> that is the whole point of a clustered index
> and by default a select statement with no ORDER BY will always return
> data in the order of the clustered index (when present)
Um. No. I've seen it return them out of order with only a few hundred
rows. As soon as the table is occupying more than one page, the query
optimizer *can* decide to produce a parallel plan. You'll see the
result as chunks of output which are in clustered index order, but no
deterministic ordering between the chunks. e.g. it'll look like:

1
2
3
4
5
11
12
13
14
15
6
7
8
9
10

The *only* way to guarantee the order of output is to put an order by
clause on your select statement.

Damien|||John Rivers wrote:
> and by default a select statement with no ORDER BY will always return
> data in the order of the clustered index (when present)

Not true at all. As Joe says, tables are not logically ordered. There
is no guarantee that any queries will match the physical order on disk
or in a clustered index.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||On 28 Apr 2006 05:41:26 -0700, John Rivers wrote:

>Hello,
>when a clustered index is present the records *are* physically ordered
>on disk to match the index
>that is the whole point of a clustered index

Hi John,

Correct.

>and by default a select statement with no ORDER BY will always return
>data in the order of the clustered index (when present)

Incorrect. Damien already pointed out the risk of parallellism.

Another potential issue is an optimization technique MS employs called
"piggybacking" - if a query on another connection is in the middle of a
tbale scan on the table you need, the DB will use the values coming in
for your query as well, then (when the first query's table scan is
finished) restart the scan from start up to where it started to
piggyback. The results would be like 6 - 7 - 8 - 9 - 10 - 1 - 2 - 3 - 4
- 5

This is almost impossible to reproduce in a test environment, but it
MIGHT happen intermittently in a heavily used production DB. Tough lluck
if your app expects the rows to be in order, even without ORDER BY.

>the issue i am trying to highlight concerns the order of the records on
>disk when a *compound* clustered index is present
>i have seen cases when it is not as expected

How did you "see" those cases? Using a query reallly doesn't prove
anything. Did you issue DBCC PAGE commands to inspect the actual
contents of the index and data pages?

--
Hugo Kornelis, SQL Server MVP|||John Rivers (first10@.btinternet.com) writes:
> when a clustered index is present the records *are* physically ordered
> on disk to match the index
> that is the whole point of a clustered index

Actually, they are ordered if you follow the page links. But if pages
are in disorder, the physical order on disk may be yet another one.

> and by default a select statement with no ORDER BY will always return
> data in the order of the clustered index (when present)

No. This may have been true by chance for SQL Server up version 6.5. It is
definitely not correct for SQL 7 and later.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your knowledgable answers

I will check out DBCC PAGE

Compound Primary Key - How to ?

I need to know how to create a compound primary key in Transact-SQL
It's really simple, it's a table with 3 column, two of those make the primary key Right now the CREATE TABLE look like this:

/* SectionsContent Table */
CREATE TABLE SectionsContent (
SectionID Int NOT NULL
CONSTRAINT FK_SectionsContent_SectionID FOREIGN KEY
REFERENCES PsychoCMS.dbo.Sections(ID),
DocumentID Int NOT NULL
CONSTRAINT FK_SectionsContent_DocumentID FOREIGN KEY
REFERENCES PsychoCMS.dbo.Documents(ID),
Position int NOT NULL
)

Can anyone help me ?One of my tricks to figure out how to do stuff like this is use Enterprise Manager to build it and then have it generate a script. You get something like this for the primary key part of the table:

ALTER TABLE [dbo].[titleauthor] ADD
CONSTRAINT [UPKCL_taind] PRIMARY KEY CLUSTERED
(
[au_id],
[title_id]
) ON [PRIMARY]
GOsqlsql

Composite Primay Key and foreign Key

Hi all,

I get a big problem when I doing my assignment.
It told me to:

- Create a table with one composite primary key
- Create another table with one primary key and one foreign key refer to last table

Here is my SQL statement
CREATE TABLE table1
(
FirstName varchar(10)
LastName varchar(10)
PRIMARY KEY (FirstName, LastName)
)

CREATE TABLE table2
(
PlanID int,
PRIMARY KEY (PlanID)
FirstName varchar(10) FOREIGN KEY REFERENCES table1(FirstName)
LastName varchar(10) FOREIGN KEY REFERENCES table1(LastName)
)

It failed. I have searched for google long time but no result for that.
Hope you can save me. Thanks!!!Your problem is probably caused because you reference a column to a partial key. What exact error do you get?

Another solution could be:

CREATE TABLE table1
(
keyField int not null
FirstName varchar(10)
LastName varchar(10)
PRIMARY KEY (keyField)
)

CREATE TABLE table2
(
PlanID int not null,
PRIMARY KEY (PlanID)
t1_keyField int FOREIGN KEY REFERENCES table1(keyField)
)

If you want the combination of the first and last name to be unique you can always create a unique constraint on the two columns.|||[QUOTE][SIZE=1]Originally posted by jora
Your problem is probably caused because you reference a column to a partial key. What exact error do you get?

The error said
"There are no primary or candidate keys in the referenced table 'table1'
that match the referencing column list in the foreign key 'FK_table2_FirstName'

Thanks for your suggestion but table1 should have a composite primary key instead of a single primary key.|||Oh! I get it.

Here is the code:
CREATE TABLE table1
(
FirstName varchar(10),
LastName varchar(10),
PRIMARY KEY (FirstName, LastName),
)

CREATE TABLE table2
(
PlanID int,
FirstName varchar(10),
LastName varchar(10),
PRIMARY KEY (PlanID),
FOREIGN KEY(FirstName,LastName) REFERENCES table1 FirstName,LastName),
)

Composite primary key on a table variable?

Is is possible to create a composite primary key on a table variable?
Neither of these two statements are successful:

DECLARE @.opmcjf TABLE (
jobdetailid INT NOT NULL,
cjfid INT NOT NULL,
cjfvalue VARCHAR(100) NULL
)

ALTER TABLE @.opmcjf ADD CONSTRAINT [PK_opmcjf] PRIMARY KEY CLUSTERED
(
[jobdetailid],
[cjfid]
)

and

DECLARE @.opmcjf TABLE (
jobdetailid INT PRIMARY KEY,
cjfid INT PRIMARY KEY,
cjfvalue VARCHAR(100) NULL
)

Thanks,
Shaun"Shaun Evans" <sevans2001@.hotmail.com> wrote in message
news:375ac918.0402240644.261460f2@.posting.google.c om...
> Is is possible to create a composite primary key on a table variable?
> Neither of these two statements are successful:
> DECLARE @.opmcjf TABLE (
> jobdetailid INT NOT NULL,
> cjfid INT NOT NULL,
> cjfvalue VARCHAR(100) NULL
> )
> ALTER TABLE @.opmcjf ADD CONSTRAINT [PK_opmcjf] PRIMARY KEY CLUSTERED
> (
> [jobdetailid],
> [cjfid]
> )
> and
> DECLARE @.opmcjf TABLE (
> jobdetailid INT PRIMARY KEY,
> cjfid INT PRIMARY KEY,
> cjfvalue VARCHAR(100) NULL
> )
> Thanks,
> Shaun

This should work - see "table" in Books Online.

DECLARE @.opmcjf TABLE (
jobdetailid INT NOT NULL,
cjfid INT NOT NULL,
cjfvalue VARCHAR(100) NULL,
primary key ([jobdetailid],[cjfid])
)

Simon|||This is the syntax:

DECLARE @.opmcjf TABLE (jobdetailid INTEGER, cjfid INTEGER, cjfvalue
VARCHAR(100) NULL, PRIMARY KEY (jobdetailid,cjfid))

AFAIK it isn't possible to alter a table-variable after it's declared. Since
a table-variable is scoped to a batch I guess that functionality wouldn't be
very useful.

--
David Portas
SQL Server MVP
--

compond index and key faster/better?

In a situation where you have two tables in a hierarchy like this:

create table authors (authorid int identity (1,1))

create table books (
authorid int,
bookid int identity (1,1)
)

Is there any disadvantage to having the primary key and the clustered
index as a compound key, like this:

alter table books add constraint PK_books primary key clustered
(authored, bookid)

Normally, I would make bookid the key, but then I got to thinking, most
of the queries are going to be "select * from books where authorid =
@.@.some_authorID"

So, wouldn't a compound key and index make this a little faster?(eric.nave@.gmail.com) writes:
> create table books (
> authorid int,
> bookid int identity (1,1)
> )
> Is there any disadvantage to having the primary key and the clustered
> index as a compound key, like this:
> alter table books add constraint PK_books primary key clustered
> (authored, bookid)

That looks a little funny. Since bookid is unique, why add authorid
to the PK?

Then again, PK of a books table should probably be the ISBN, as that
is a natural key.

And there should probably be a relation table, as a book can more than
one author.

> Normally, I would make bookid the key, but then I got to thinking, most
> of the queries are going to be "select * from books where authorid =
> @.@.some_authorID"
> So, wouldn't a compound key and index make this a little faster?

Having the clustered index on authorid is probably better than clustering
on ISBN, yes. But there not really any reason to add it to the PK of
books. (In an bookauthors table that covers the many-to-many relation
between books and authors, the key would make sense.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> That looks a little funny. Since bookid is unique, why add authorid
> to the PK?

Only for the purpose of having the clustered index that way. I think
my example would have been better if I'd made it a case of, "should I
have a simple or a compond index" and left the key out of it. My
mistake.

> Then again, PK of a books table should probably be the ISBN, as that
> is a natural key.

Well, this is just an example off the top of my head. Books and
authors is just the first think I came up with.

> And there should probably be a relation table, as a book can more than
> one author.

Same response as above.

> Having the clustered index on authorid is probably better than clustering
> on ISBN, yes. But there not really any reason to add it to the PK of
> books.

I concede that there's no reason to have it in the PK. But, having a
compond clustered index seems like a good idea if you constantly want
to find all books by a given author. The books will then be returned
in bookid order.|||> But, having a
> compond clustered index seems like a good idea if you constantly want
> to find all books by a given author. The books will then be returned
> in bookid order.

Although it is likely that data will be returned in sequence by bookid, the
order is guaranteed only when bookid is specified in an ORDER BY clause. A
clustered index including bookid will facilitate efficient ordering in this
case.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<eric.nave@.gmail.com> wrote in message
news:1125527844.570677.118750@.z14g2000cwz.googlegr oups.com...
> Erland Sommarskog wrote:
>> That looks a little funny. Since bookid is unique, why add authorid
>> to the PK?
> Only for the purpose of having the clustered index that way. I think
> my example would have been better if I'd made it a case of, "should I
> have a simple or a compond index" and left the key out of it. My
> mistake.
>> Then again, PK of a books table should probably be the ISBN, as that
>> is a natural key.
> Well, this is just an example off the top of my head. Books and
> authors is just the first think I came up with.
>> And there should probably be a relation table, as a book can more than
>> one author.
> Same response as above.
>> Having the clustered index on authorid is probably better than clustering
>> on ISBN, yes. But there not really any reason to add it to the PK of
>> books.
> I concede that there's no reason to have it in the PK. But, having a
> compond clustered index seems like a good idea if you constantly want
> to find all books by a given author. The books will then be returned
> in bookid order.

Complicated Update query based on existing data

OK folks, may have a tough one or perhaps just not thinking it through
well. I need to create a SQL query or queries that updates two columns
based on some business rules. Here's an example of the data:
GroupID complete_num first_num second_num InUse biggest
965423 1.0 1 0
965423 2.0 2 0
965423 3.0 3 0 X
965423 3.1 3 1
965423 3.2 3 2 X
324554 1.0 1 0
324554 2.0 2 0 X X
123456 0.1 0 1
123456 0.2 0 2 X X
Hopefully the above even vaguely lines up for you. The last two
columns are currently blank. The representation above is how I would
like them to look after the queries run. So for the above data, you
have a group id that links all records for one set together. I need to
have the "InUse" box updated with a value of "X" for the highest number
that has zero in the second_num column for a group. I also need the
biggest column set to "X" for the largest number in a particular group,
so 3.1 is larger than 3.0. Keep in mind, I have separated the
complete_num into two columns as there could be a "decimal" value of
"10" which is higher than "1". They are not the same as complete_num
is not really a decimal numeric representation. It's used
programatically for other things. Seperating into two separate columns
allows better sorting of data as complete_num is varchar and first and
second num are integer. You will also see the case, 123456,where there
is no zero in the second_num column. In this case, the highest
second_num value will have both set to "X". Any help would be
appreciated and SQL queries are prefered over any procedures/functions
as this is a one time query I need to run against the database. If you
need further clarification or more examples, please let me know.
Thanks.
JRAlso note that there is of course a unique incremental column in the
table. For arguements sake we can say U_ID. Also these records could
be in any order by default in the database. Not necessarily grouped
together as shown above.|||JR (jriker1@.yahoo.com) writes:
> OK folks, may have a tough one or perhaps just not thinking it through
> well. I need to create a SQL query or queries that updates two columns
> based on some business rules. Here's an example of the data:
> GroupID complete_num first_num second_num InUse biggest
> 965423 1.0 1 0
> 965423 2.0 2 0
> 965423 3.0 3 0 X
> 965423 3.1 3 1
> 965423 3.2 3 2 X
> 324554 1.0 1 0
> 324554 2.0 2 0 X X
> 123456 0.1 0 1
> 123456 0.2 0 2 X X
> Hopefully the above even vaguely lines up for you. The last two
> columns are currently blank. The representation above is how I would
> like them to look after the queries run. So for the above data, you
> have a group id that links all records for one set together. I need to
> have the "InUse" box updated with a value of "X" for the highest number
> that has zero in the second_num column for a group. I also need the
> biggest column set to "X" for the largest number in a particular group,
> so 3.1 is larger than 3.0.
It is always a good idea for this sort of question to include CREATE
TABLE statements for the table, and the sample data as INSERT statements.
That makes it easy to copy-and-paste into a query tool, to develop a
tested solution.
Thus, this is an untested solution:
BEGIN TRANSACTION
UPDATE tbl
SET biggest = 0,
inuse = 0
UPDATE tbl
SET biggest = 1
FROM tbl a
WHERE EXISTS (SELECT *
FROM (SELECT GroupID,
biggest = MAX(100000 * first_num + second_num)
FROM tbl
GROUP BY GroupID) AS big
WHERE a.big = big.GroupID
AND a.first_num * 1000000 + a.second_num = big.biggest)
UPDATE tbl
SET inuse = 1
FROM tbl a
JOIN (SELECT GroupID, first_num = MAX(first_num)
FROM tbl
WHERE second_num = 0
GROUP BY GroupID) AS inuse ON a.GroupID = inuse.GroupID
AND a.first_num = inuse.first_num
AND a.second_num = 0
UPDATE tbl
SET inuse = 1
FROM tbl a
WHERE a.biggest = 1
AND NOT EXISTS (SELECT *
FROM tbl b
WHERE a.GroupID = b.GroupID
AND a.insue = 1)
COMMIT TRANSACTION
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland. SQL Query Analyser is complaining about a syntax
problem with the '=' on "biggest = MAX(100000 * first_num +
second_num)" in the second update and "JOIN (SELECT GroupID,
first_num = MAX(first_num)" in the ghird.|||JR,
I think you can say this more simply: Update InUse to X
for the largest second_num value in the group *counting 0
as the largest*, and the largest first_num value in the case of
a tie. Update biggest to X for the largest first_num value
in the group, and the largest second_num value in the case
of a tie.
There is a more compact solution than Erland's in SQL Server 2005:
with T2 as (
select
*,
rank() over (partition by GroupID order by first_num desc,
case when second_num = 0 then 2147364827 else second_num end desc)
as rk1,
rank() over (partition by GroupID order by second_num desc,
first_num desc) as rk2
from #T
)
update T2 set
InUse = case when rk1 = 1 then 'X' else InUse end,
biggest = case when rk2 = 1 then 'X' else biggest end
where rk1 = 1 or rk2 = 1
Steve Kass
Drew University
JR wrote:

>OK folks, may have a tough one or perhaps just not thinking it through
>well. I need to create a SQL query or queries that updates two columns
>based on some business rules. Here's an example of the data:
>GroupID complete_num first_num second_num InUse biggest
>965423 1.0 1 0
>965423 2.0 2 0
>965423 3.0 3 0 X
>965423 3.1 3 1
>965423 3.2 3 2 X
>324554 1.0 1 0
>324554 2.0 2 0 X X
>123456 0.1 0 1
>123456 0.2 0 2 X X
>
>Hopefully the above even vaguely lines up for you. The last two
>columns are currently blank. The representation above is how I would
>like them to look after the queries run. So for the above data, you
>have a group id that links all records for one set together. I need to
>have the "InUse" box updated with a value of "X" for the highest number
>that has zero in the second_num column for a group. I also need the
>biggest column set to "X" for the largest number in a particular group,
>so 3.1 is larger than 3.0. Keep in mind, I have separated the
>complete_num into two columns as there could be a "decimal" value of
>"10" which is higher than "1". They are not the same as complete_num
>is not really a decimal numeric representation. It's used
>programatically for other things. Seperating into two separate columns
>allows better sorting of data as complete_num is varchar and first and
>second num are integer. You will also see the case, 123456,where there
>is no zero in the second_num column. In this case, the highest
>second_num value will have both set to "X". Any help would be
>appreciated and SQL queries are prefered over any procedures/functions
>as this is a one time query I need to run against the database. If you
>need further clarification or more examples, please let me know.
>Thanks.
>JR
>
>|||On 9 Apr 2006 07:36:13 -0700, JR wrote:

>OK folks, may have a tough one or perhaps just not thinking it through
>well. I need to create a SQL query or queries that updates two columns
>based on some business rules. Here's an example of the data:
(snip)
Hi JR,
If these columns should be calculated based on the data, why store them
at all? I would only recommend that if updates are very infrequent,
queries of the data are very frequent and the table is large, or if
these columns need to capture a moment in time and stay unchanged after
that, even if the underlying data does change.
In one of the latter cases, use Erland's suggestion. Otherwise, drop the
columns from the table and set up a view instead:
CREATE VIEW ChooseGoodName
AS
SELECT a.GroupID, a.complete_num, a.first_num, a.second_num,
CASE WHEN a.first_num = b.first_num
AND a.second_num = 0
THEN 'X' -- Latest X.0
WHEN a.first_num = 0
AND a.second_num = b.second_num
THEN 'X' -- Latest 0.Y
ELSE ''
END AS InUse,
CASE WHEN b.first_num = a.first_num
AND b.second_num = a.second_num
THEN 'X'
ELSE ''
END AS biggest
FROM YourTable AS a
INNER JOIN (SELECT GroupID, complete_num, first_num, second_num
FROM YourTable AS c
WHERE NOT EXISTS (SELECT *
FROM YourTable AS d
WHERE d.GroupID = c.GroupID
AND ( d.first_num > c.first_num
OR ( d.first_num = c.first_num
AND d.second_num > c.second_num))
) ) AS b
ON b.GroupID = a.GroupID
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||JR (jriker1@.yahoo.com) writes:
> Thanks Erland. SQL Query Analyser is complaining about a syntax
> problem with the '=' on "biggest = MAX(100000 * first_num +
> second_num)" in the second update and "JOIN (SELECT GroupID,
> first_num = MAX(first_num)" in the ghird.
Yes, as I said the code was untested for reasons I explained. I assume
that you are able to weed out trivial syntax errors on your own. If not,
please include CREATE TABLE statements and the sample data in INSERT
statements, to make testing easy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||As requested, here is a table creation script and some minimal data to
limit the length of the message. Also Hugo's view is great however
since this data is for one time use, would imagine updates to the
existing data would be preferable over introducing a new view into the
mix. If not updating the data directly based on the view would now be
a simple matter when you introduce the Id from the original table into
the view.
CREATE TABLE [abcd].[dbo].[TBL1
(Id,GroupId,complete_num,first_num,secon
d_num)] (
[Id] int NOT NULL,
[GroupID] nvarchar (60) NULL,
[complete_num] varchar (255) NULL,
[first_num] integer,
[second_num] integer,
[InUse] varchar (2) NULL,
[biggest] varchar (2) NULL,
)
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(91510,ABC1235,2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(89377,ABC1235,2.1,2,1);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(89371,ABC1235,2.2,2,2);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1310,M123456,1.0,1,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1309,M123456,2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1311,M123456,3.0,3,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1312,M123456,4.0,4,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1315,M123456,5.0,5,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1318,M123456,6.0,6,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1319,M123456,7.0,7,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1317,M123456,8.0,8,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(5342,M123456,9.0,9,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(5346,M123456,10.0,10,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(5756,M123456,11.0,11,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(6315,M123456,12.0,12,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(6604,M123456,13.0,13,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(6920,M123456,14.0,14,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1002,M123456,15.0,15,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(4023,ARDFO32,0.1,0,1);|||Your programs will be total nightmares and crap until you learn how to
design a schema.
Look at the DDL; do you really have a NCHAR(60) grpoup identifier? In
Chinese' Wel;l;, since you allowed it, you will get one! Why do you
have redundant split attributes (i.e. first_num || second_num =
complete_num)? Let's spit on normalization!! I also love the clear,
meaningful names of the data elements. Tell us what a thing is, not
its sequential order inside another column. Logical not physical
descriptions.
There are no keys, no constraints. This is not a table at all! And
you invented your own syntax for CREATE TABLE.
Your idea of updatind computed columns is a way to mimic punch cards.
Back in the 1950-60's we had to store those things in the physical
card, like you are doing now.
Making a guess, if you normalized your schema, had a key and followed
the baisc data modeling rules, would this nightmare look more like
this? Better names that show subordination (well, Foobar is a dummy
name, but that is all you gave us)
CREATE TABLE Foobar
(group_id CHAR(6) NOT NULL
CHECK (group_id LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')
section_nbr INTEGER DEFAULT 1 NOT NULL
CHECK (section_num >= 0),
subsection_nbr INTEGER DEFAULT 0 NOT NULL
CHECK (subsection_num >= 0),
PRIMARY KEY (group_id, section_nbr, subsection_nbr));
Do you need a constaint to assure that the subsections are in sequence?
Is there a check digit rule in the group_id? 90% of the work in RDBMS
is done in the DDL!!
SQL does not have links; it has REFERENCES and grouping. Totally
different concepts, based on sets and not pre-RDBMS file and pointer
systems. Rows are nothing whatsoever like records.
Now, the answer to your question is a VIEW, not a "punch cards and bit
flags" solution via updates.
CREATE VIEW InUseFoobar (group_id, section_nbr, subsection_nbr)
AS
SELECT group_id, MAX(section_nbr), 0
FROM Foobar
WHERE subsection = 0
GROUP BY group_id) ;
CREATE VIEW MaxFoobar (group_id, section_nbr, subsection_nbr)
AS
SELECT group_id, section_nbr, MAX(subsection_nbr)
FROM Foobar AS F1, InUseFoobar AS U1
WHERE F1.group_id = .U1.group_id
AND F1.section_nbr = .U1.section_nbr
GROUP BY group_id, section_nbr ;
<<untested>>|||JR (jriker1@.yahoo.com) writes:
> As requested, here is a table creation script and some minimal data to
> limit the length of the message. Also Hugo's view is great however
> since this data is for one time use, would imagine updates to the
> existing data would be preferable over introducing a new view into the
> mix. If not updating the data directly based on the view would now be
> a simple matter when you introduce the Id from the original table into
> the view.
Below is a tested version of my script. I'm not sure that I understand
the syntax errors you mentioned; I did not get these. I include
your original script, with small changes. Note that I've made inuse
and biggest into bit columns; I did this as I used 0 and 1 in my script.
Beware that things get wrapped in news transport!
CREATE TABLE [dbo].[TBL1]
(
[Id] int NOT NULL,
[GroupId] nvarchar (60) NULL,
[complete_num] varchar (255) NULL,
[first_num] integer,
[second_num] integer,
[inuse] bit NULL,
[biggest] bit NULL,
)
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (9151
0,'ABC1235',2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (8937
7,'ABC1235',2.1,2,1);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (8937
1,'ABC1235',2.2,2,2);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1310
,'M123456',1.0,1,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1309
,'M123456',2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1311
,'M123456',3.0,3,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1312
,'M123456',4.0,4,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1315
,'M123456',5.0,5,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1318
,'M123456',6.0,6,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1319
,'M123456',7.0,7,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1317
,'M123456',8.0,8,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (5342
,'M123456',9.0,9,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (5346
,'M123456',10.0,10,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (5756
,'M123456',11.0,11,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (6315
,'M123456',12.0,12,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (6604
,'M123456',13.0,13,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (6920
,'M123456',14.0,14,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1002
,'M123456',15.0,15,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (4023
,'ARDFO32',0.1,0,1);
go
BEGIN TRANSACTION
UPDATE TBL1
SET biggest = 0,
inuse = 0
UPDATE TBL1
SET biggest = 1
FROM TBL1 a
WHERE EXISTS (SELECT *
FROM (SELECT GroupId,
biggest = MAX(100000 * first_num + second_num)
FROM TBL1
GROUP BY GroupId) AS big
WHERE a.GroupId = big.GroupId
AND a.first_num * 100000 + a.second_num = big.biggest)
UPDATE TBL1
SET inuse = 1
FROM TBL1 a
JOIN (SELECT GroupId, first_num = MAX(first_num)
FROM TBL1
WHERE second_num = 0
GROUP BY GroupId) AS inuse ON a.GroupId = inuse.GroupId
AND a.first_num = inuse.first_num
AND a.second_num = 0
UPDATE TBL1
SET inuse = 1
FROM TBL1 a
WHERE a.biggest = 1
AND NOT EXISTS (SELECT *
FROM TBL1 b
WHERE a.GroupId = b.GroupId
AND b.inuse = 1)
COMMIT TRANSACTION
SELECT * FROM TBL1
go
drop table TBL1
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Complicated Connection Problem between ADP and SQL Server

About 3 years ago, I was told to create a SQL Server 2000 database for a
client of ours, and to set up an Access XP/2002 project (adp) front end that
would be used on their network to interact with the data. This database
needs to be updated every six months. Since there has been some "feature
creep" in the front end, I have almost always needed to install a new adp
front end on the 5-8 workstations where the users happen to sit.
I'm not a network guy by any means. This client has thousands of
workstations in at least two different building, so they provided all the
expertise for getting the user workstations talking to the SQL Server. Most
of the time, I just walk in with a back-up of the new database, restore it
onto their server, slap a new front end on the 5-8 workstations that will
need to work with the database, and off I go.
The very first time that we ever did this installation, there were some
problems getting the workstations to talk to the server. The guy I was
working with did some magic on the workstations and the connections worked
just fine after that. Then he moved onto a new job, and they assigned
someone else to work with me during my twice-yearly visits. All of the
workstations that had been used before would work just fine, the connections
would go thorugh without any effort on our part, and the installation was
easy. But whenever one of the workers had been given a new computer, the
connection would fail and the adp wouldn't be able to talk to the server.
This wasn't a problem, though, since the guy I was working with also seemed
to know what to do. He would get on the workstation, do his magic, the
connection would be established, and that workstation would never again
cause us any problems on any future vists.
This week I made my latest visit to the client's offices. The guy I had
been working with us died unexpectedly a few months ago, and nobody there
seems to have any idea what he did to make these virgin machines -- the ones
that had never been used to work with this database in the past -- talk to
the SQL Server. And of course, two of the five users that need to work
with the database have recently received new computers.
Since I don't have anyone there who can fix the problem on their end, and
since it was probably sloppy of me to require them to alter their
workstation setup in the first place, I was wondering whether the problem
might be in my ADO connection string. Maybe I could use a different string
that would connect just fine, without any requirement to change any settings
on the workstation.
The SQL Server at the client uses mixed-mode security. My project opens up
with a custom login form to get the username and password. Then it calls
the following function to establish the connection for the project. Note
that I first open up a generic ADO connection (cnnTest) to the database to
make sure that it works and to trap any errors that might come up. Then, if
that test connection works, I go ahead and call
CurrentProject.OpenConnection using the same string.
-- BEGIN VBA CODE --
Public Function EstablishConnection(ByVal user As String, _
ByVal password As String, _
Optional ByVal displayWarnings As
Boolean = True, _
Optional ByVal finalTry As Boolean =
False) As Boolean
Dim strServer As String
Dim strDatabase As String
Dim strConnect As String
Dim cnnTest As ADODB.Connection
Call DoCmd.Hourglass(True)
strServer = SERVER_NAME
strDatabase = "PbcPrimary"
strConnect = "Provider=SQLOLEDB;Data Source=" & strServer & ";Initial
Catalog=" & strDatabase & _
";Persist Security Info=FALSE"
Set cnnTest = New ADODB.Connection
On Error GoTo LoginFailure
Call cnnTest.Open(strConnect, user, password)
On Error GoTo 0
Set cnnTest = Nothing
'If things have progressed to this point, then cnnTest has been successfully
established. Switch the
'CurrentProject's connection to this new connection.
Call CurrentProject.OpenConnection(strConnect, user, password)
EstablishConnection = True
Call DoCmd.Hourglass(False)
Exit Function
' - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - -
LoginFailure:
Call DoCmd.Hourglass(False)
If displayWarnings Then
If Err.Number = -2147217843 Then
If Not finalTry Then
Call MsgBox("The user name and password that you entered are not
valid. Please " & _
"check your entries and try again.", vbExclamation, "Login Failed")
Else
Call Fatal("The user names and passwords that you have entered were
not valid. If " & _
"you have forgotten your user name or password, please see the
local JRC site manager.", _
"Shutting Down")
End If
ElseIf Err.Number = -2147467259 And LTest(Err.Description, "Cannot open
database") Then
'Note that you get this error for at least two conditions. First,
this error occurs if the server is found,
'but the database cannot be. Secondly, it occurs if the user's login
exists on the server, but the login
'has not been given permission to access the database. Since the
first situation seems more likely, that is
'the problem that is being described in the MsgBox.
Call Fatal("The reconciliation database could not be found on the
database server.", _
"Database Missing")
ElseIf Err.Number = -2147467259 Then
Call Fatal("The " & strServer & " database server could not be located
on the " & _
"network.", "Server Not Found")
Else
Call Unexpected("frmLogin, cmdOK_Click", Err.Number, Err.Description,
"trying to " & _
"connect to the reconciliation database", "Connection Failed")
End If
End If
EstablishConnection = False
End Function
-- END VBA CODE --
As I said, this code works just fine on any of the "experienced" machines,
but the new ones don't like it. On the "virgin" workstations, I get an
error -2147467259, [DBNMPNTW]ConnectionOpen (CreateFile()). Because of my
error-handling routine, this gets reported to the user as "The database
server could not be located on the network".
I did some research on this error. It has something to do with "named
pipes", and I'm sure many of you actually understand what it means. But I'm
not a network guy. Moreover, this isn't my server. I can't just go into
the server and change the settings to allow the use of named pipes. So I
thought that I should instead change my connection string to something that
the server and the network would accept.
I created a brand new adp on one of the virgin workstations, binded it to
the target database on the SQL server, and found that I was indeed able to
connect to the server and the database without any problem at all from
within this new project. When I checked the connection string of this bound
project, I found the following:
Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data
Source=ClientServerName;User ID=MyUserName;Initial Catalog=PbcPrimary;Data
Provider=SQLOLEDB.1
This string has me puzzled a bit. I don't understant why what I thought was
the "Provider" has now become the "Data Provider", and I really don't
undertand why the provider is now something that is native to Access iteslf
instead of the SQL Server OLEDB provider. But what was even stranger was
what I found when I checked the BaseConnectionString property of the bound
project:
PROVIDER=SQLOLEDB.1;PERSIST SECURITY INFO=FALSE;INITIAL
CATALOG=PbcPrimary;DATA SOURCE=ClientServerName;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=MyWorkstationName
Even after reading the on-line help file, I still don't really understand
what the difference is between CurrentProject.BaseConnectionString and
CurrentProject.Connection.ConnectionString. Maybe someone here can
enlighten me.
But here is where things really get weird. When I copied the working
connection string from the bound project into my own project, it still
didn't work. When I checked all of the connection properties (under the
File menu) of the working bound project, I found that it was using a
different network library. I can't remember which one specifially, but it
definitely wasn't "DBNMPNTW" and I think it was probably "DBMSSOCN".
And now things get really strange. I added in the command "Network
Library=DBMSSOCN" into my connection string. When I did that, my inital
test ADO connection (cnnTest) worked just fine. Success! But when the
program then tried to use the exact same connection string in the
CurrentProject.OpenConnection method, I got an entirely new error. Since
I'm no longer at the client site, I don't have the exact text of the error,
but it was something along the lines of "-2147467259 (8004005) Client unable
to establish connection".
Why would a regular ADO connection work, but the project connection fail?
Does anyone know of some connection string settings that might work here?
Alternately, maybe somebody knows or can guess what kind of workstation
magic was done to allow the connection on the "experienced" computers. We
did find one thing. All of the older machines have a DSN which points
directly at the SQL Server. But the newer machines have no such DSN. So I
think it has something to do with the pressence of this DSN on the older
machines. But we couldn't create a DSN on the newer machines. We got the
same "Client unable to establish connection" error when we tried. So whle
the DSN might be necessary, it's also the case that something else has to be
done before the DSN can be created. Then again, the connection string I'm
using is DSN-less, so maybe the DSNs were just there because the guy who
fixed the workstations for me used DSNs to make sure his fix was working.
First of all, have you checked out the name resolution for the server
names you're using? If named pipes aren't doing the trick (and I don't
know much about them) then perhaps making sure the name can resolve to
an IP address could help. Also, for testing purposes, use the IP
address instead of the server name and see if that helps.
Second, I'm not sure if this will help, but... here are some connection
strings that work for me with an ADP connection to a SQL 2000 database.
?currentproject.BaseConnectionString
PROVIDER=SQLOLEDB.1;DATA SOURCE=TheServerName;USER
ID=MyUserName;PASSWORD=MyPassword;INITIAL CATALOG=TheDatabase
?currentproject.Connection.ConnectionString
Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=True;Data
Source=TheServerName;User ID=MyUserName;Password=MyPassword;Initial
Catalog=TheDtabase;Data Provider=SQLOLEDB.1
In this case TheServerName is a name that can be found by pinging or
doing a Start->Run "\\MyServerName".
I also have a SQL Server Client Network tool which lets me use any
stinking name I want... or you can host->IP into the "hosts." file
(commonly found at windows\system32\etc\drivers\hosts or something.).
I hope this helps.
Oh, another thing that might help. In your ADP, go to
Tools->Options->Pages and click "Use Default Connection File" then
Browse. Start with the New Source button before you start messing with
existing odc or udl files. Perhaps the wizard will help you come up
with something.
And, I'm sure you've also gone to File->Connection and played with the
settings there?
Once you have a connection that works, you can go to the immediate pane
in the VBE IDE and print out the connection strings. That will help you
learn what you need to build your own connection strings.
Erik
ESquared
ESquared's Profile: http://www.dbtalk.net/m3
View this thread: http://www.dbtalk.net/t285429

Complicated Connection Problem between ADP and SQL Server

About 3 years ago, I was told to create a SQL Server 2000 database for a
client of ours, and to set up an Access XP/2002 project (adp) front end that
would be used on their network to interact with the data. This database
needs to be updated every six months. Since there has been some "feature
creep" in the front end, I have almost always needed to install a new adp
front end on the 5-8 workstations where the users happen to sit.
I'm not a network guy by any means. This client has thousands of
workstations in at least two different building, so they provided all the
expertise for getting the user workstations talking to the SQL Server. Most
of the time, I just walk in with a back-up of the new database, restore it
onto their server, slap a new front end on the 5-8 workstations that will
need to work with the database, and off I go.
The very first time that we ever did this installation, there were some
problems getting the workstations to talk to the server. The guy I was
working with did some magic on the workstations and the connections worked
just fine after that. Then he moved onto a new job, and they assigned
someone else to work with me during my twice-yearly visits. All of the
workstations that had been used before would work just fine, the connections
would go thorugh without any effort on our part, and the installation was
easy. But whenever one of the workers had been given a new computer, the
connection would fail and the adp wouldn't be able to talk to the server.
This wasn't a problem, though, since the guy I was working with also seemed
to know what to do. He would get on the workstation, do his magic, the
connection would be established, and that workstation would never again
cause us any problems on any future vists.
This week I made my latest visit to the client's offices. The guy I had
been working with us died unexpectedly a few months ago, and nobody there
seems to have any idea what he did to make these virgin machines -- the ones
that had never been used to work with this database in the past -- talk to
the SQL Server. And of course, two of the five users that need to work
with the database have recently received new computers.
Since I don't have anyone there who can fix the problem on their end, and
since it was probably sloppy of me to require them to alter their
workstation setup in the first place, I was wondering whether the problem
might be in my ADO connection string. Maybe I could use a different string
that would connect just fine, without any requirement to change any settings
on the workstation.
The SQL Server at the client uses mixed-mode security. My project opens up
with a custom login form to get the username and password. Then it calls
the following function to establish the connection for the project. Note
that I first open up a generic ADO connection (cnnTest) to the database to
make sure that it works and to trap any errors that might come up. Then, if
that test connection works, I go ahead and call
CurrentProject.OpenConnection using the same string.
-- BEGIN VBA CODE --
Public Function EstablishConnection(ByVal user As String, _
ByVal password As String, _
Optional ByVal displayWarnings As
Boolean = True, _
Optional ByVal finalTry As Boolean =
False) As Boolean
Dim strServer As String
Dim strDatabase As String
Dim strConnect As String
Dim cnnTest As ADODB.Connection
Call DoCmd.Hourglass(True)
strServer = SERVER_NAME
strDatabase = "PbcPrimary"
strConnect = "Provider=SQLOLEDB;Data Source=" & strServer & ";Initial
Catalog=" & strDatabase & _
";Persist Security Info=FALSE"
Set cnnTest = New ADODB.Connection
On Error GoTo LoginFailure
Call cnnTest.Open(strConnect, user, password)
On Error GoTo 0
Set cnnTest = Nothing
'If things have progressed to this point, then cnnTest has been successfully
established. Switch the
'CurrentProject's connection to this new connection.
Call CurrentProject.OpenConnection(strConnect, user, password)
EstablishConnection = True
Call DoCmd.Hourglass(False)
Exit Function
' - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - -
LoginFailure:
Call DoCmd.Hourglass(False)
If displayWarnings Then
If Err.Number = -2147217843 Then
If Not finalTry Then
Call MsgBox("The user name and password that you entered are not
valid. Please " & _
"check your entries and try again.", vbExclamation, "Login Failed")
Else
Call Fatal("The user names and passwords that you have entered were
not valid. If " & _
"you have forgotten your user name or password, please see the
local JRC site manager.", _
"Shutting Down")
End If
ElseIf Err.Number = -2147467259 And LTest(Err.Description, "Cannot open
database") Then
'Note that you get this error for at least two conditions. First,
this error occurs if the server is found,
'but the database cannot be. Secondly, it occurs if the user's login
exists on the server, but the login
'has not been given permission to access the database. Since the
first situation seems more likely, that is
'the problem that is being described in the MsgBox.
Call Fatal("The reconciliation database could not be found on the
database server.", _
"Database Missing")
ElseIf Err.Number = -2147467259 Then
Call Fatal("The " & strServer & " database server could not be located
on the " & _
"network.", "Server Not Found")
Else
Call Unexpected("frmLogin, cmdOK_Click", Err.Number, Err.Description,
"trying to " & _
"connect to the reconciliation database", "Connection Failed")
End If
End If
EstablishConnection = False
End Function
-- END VBA CODE --
As I said, this code works just fine on any of the "experienced" machines,
but the new ones don't like it. On the "virgin" workstations, I get an
error -2147467259, [DBNMPNTW]ConnectionOpen (CreateFile()). Because of
my
error-handling routine, this gets reported to the user as "The database
server could not be located on the network".
I did some research on this error. It has something to do with "named
pipes", and I'm sure many of you actually understand what it means. But I'm
not a network guy. Moreover, this isn't my server. I can't just go into
the server and change the settings to allow the use of named pipes. So I
thought that I should instead change my connection string to something that
the server and the network would accept.
I created a brand new adp on one of the virgin workstations, binded it to
the target database on the SQL server, and found that I was indeed able to
connect to the server and the database without any problem at all from
within this new project. When I checked the connection string of this bound
project, I found the following:
Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data
Source=ClientServerName;User ID=MyUserName;Initial Catalog=PbcPrimary;Data
Provider=SQLOLEDB.1
This string has me puzzled a bit. I don't understant why what I thought was
the "Provider" has now become the "Data Provider", and I really don't
undertand why the provider is now something that is native to Access iteslf
instead of the SQL Server OLEDB provider. But what was even stranger was
what I found when I checked the BaseConnectionString property of the bound
project:
PROVIDER=SQLOLEDB.1;PERSIST SECURITY INFO=FALSE;INITIAL
CATALOG=PbcPrimary;DATA SOURCE=ClientServerName;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=MyWorkstationName
Even after reading the on-line help file, I still don't really understand
what the difference is between CurrentProject.BaseConnectionString and
CurrentProject.Connection.ConnectionString. Maybe someone here can
enlighten me.
But here is where things really get weird. When I copied the working
connection string from the bound project into my own project, it still
didn't work. When I checked all of the connection properties (under the
File menu) of the working bound project, I found that it was using a
different network library. I can't remember which one specifially, but it
definitely wasn't "DBNMPNTW" and I think it was probably "DBMSSOCN".
And now things get really strange. I added in the command "Network
Library=DBMSSOCN" into my connection string. When I did that, my inital
test ADO connection (cnnTest) worked just fine. Success! But when the
program then tried to use the exact same connection string in the
CurrentProject.OpenConnection method, I got an entirely new error. Since
I'm no longer at the client site, I don't have the exact text of the error,
but it was something along the lines of "-2147467259 (8004005) Client unable
to establish connection".
Why would a regular ADO connection work, but the project connection fail?
Does anyone know of some connection string settings that might work here?
Alternately, maybe somebody knows or can guess what kind of workstation
magic was done to allow the connection on the "experienced" computers. We
did find one thing. All of the older machines have a DSN which points
directly at the SQL Server. But the newer machines have no such DSN. So I
think it has something to do with the pressence of this DSN on the older
machines. But we couldn't create a DSN on the newer machines. We got the
same "Client unable to establish connection" error when we tried. So whle
the DSN might be necessary, it's also the case that something else has to be
done before the DSN can be created. Then again, the connection string I'm
using is DSN-less, so maybe the DSNs were just there because the guy who
fixed the workstations for me used DSNs to make sure his fix was working.First of all, have you checked out the name resolution for the server
names you're using? If named pipes aren't doing the trick (and I don't
know much about them) then perhaps making sure the name can resolve to
an IP address could help. Also, for testing purposes, use the IP
address instead of the server name and see if that helps.
Second, I'm not sure if this will help, but... here are some connection
strings that work for me with an ADP connection to a SQL 2000 database.
?currentproject.BaseConnectionString
PROVIDER=SQLOLEDB.1;DATA SOURCE=TheServerName;USER
ID=MyUserName;PASSWORD=MyPassword;INITIA
L CATALOG=TheDatabase
?currentproject.Connection.ConnectionString
Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=True;Data
Source=TheServerName;User ID=MyUserName;Password=MyPassword;Initia
l
Catalog=TheDtabase;Data Provider=SQLOLEDB.1
In this case TheServerName is a name that can be found by pinging or
doing a Start->Run "\\MyServerName".
I also have a SQL Server Client Network tool which lets me use any
stinking name I want... or you can host->IP into the "hosts." file
(commonly found at windows\system32\etc\drivers\hosts or something.).
I hope this helps.
Oh, another thing that might help. In your ADP, go to
Tools->Options->Pages and click "Use Default Connection File" then
Browse. Start with the New Source button before you start messing with
existing odc or udl files. Perhaps the wizard will help you come up
with something.
And, I'm sure you've also gone to File->Connection and played with the
settings there?
Once you have a connection that works, you can go to the immediate pane
in the VBE IDE and print out the connection strings. That will help you
learn what you need to build your own connection strings.
Erik
ESquared
---
ESquared's Profile: http://www.dbtalk.net/m3
View this thread: http://www.dbtalk.net/t285429