Max Query: multiple

Showing posts with label multiple. Show all posts

Wednesday, March 28, 2012

Help with SQL 2005 Report Designer with multiple data sets

Hi,
I am trying to create a drilthrough report in report designer. I know
ho to do it if I have a single query but I do not know how to do it if
I have multiple. What is happening is that I tried to create a signle
query where I can then create a groupings and then have the report
drill through the groupings but while trying to create my query I
found that I could not create the query that I wanted being that I was
getting duplicate results in my comments column (I wanted to also add
the comments). If the user did not make a comment then it was
automatically added with the first comment made in the for that
grouping.
So I decided to make two queries. One is for selecting all of the
pending items (workflow based system) and the other for all of the
comments made for each workflow (for this report they are called
ECO). The queries work just fine if they run seperatly in the report,
but if I choose to group them so that there is a expand or cllapse
button under the user then I get the following errors:
[rsPostSortAggregateInGroupFilterExpression] A grouping filter
expression for the table =18table1=19 uses aggregate function First, Last
or Previous. These aggregate functions cannot be used in grouping
filters.
[rsAggregateInGroupExpression] A group expression for the table
'table1' includes an aggregate function. Aggregate functions cannot
be used in group expressions.
Build complete -- 2 errors, 0 warnings
The following are my new datasets:
-- dataset 1 selecting all of the ECOs that are in pending status
SELECT dbo.ChangeType.Type, SignoffStages.StageName as [Stage Name],
dbo.ChangeEntry.Number as [ECO Number],
dbo.ChangePriority.Priority, dbo.UserProfile.FullName as [Full
Name], dbo.UserProfile.Email, UserProfile_1.FullName AS Initiator,
UserProfile_1.Email AS [Initiator's Email],
dbo.ChangeEntry.RaisedOn as Initiated, dbo.ChangeSignoff.StageActive,
dbo.ChangeSignoff.StageOrder,
dbo.ChangeSignoff.DateSigned, dbo.ChangeEntry.Reason,
dbo.ChangeEntry.Description, dbo.ChangeStatus.Status
FROM dbo.ChangeEntry INNER JOIN
dbo.ChangePriority ON dbo.ChangeEntry.Priority =3D dbo.ChangePriority.ID
INNER JOIN
dbo.ChangeSignoff ON dbo.ChangeEntry.ID =3D dbo.ChangeSignoff.ECOID
INNER JOIN
dbo.UserProfile ON dbo.ChangeSignoff.UserID =3D dbo.UserProfile.ID INNER
JOIN
dbo.ChangeType ON dbo.ChangeEntry.ChangeType =3D dbo.ChangeType.ID INNER
JOIN
dbo.UserProfile UserProfile_1 ON dbo.ChangeEntry.RaisedBy =3D
UserProfile_1.ID INNER JOIN
dbo.SignoffStages ON ChangeSignoff.StageID =3D SignoffStages.ID INNER
JOIN
dbo.ChangeStatus ON dbo.ChangeEntry.Status =3D dbo.ChangeStatus.ID
WHERE dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D 1
AND ChangeType.Type like '%CPD%' AND ChangeStatus.Status =3D 'Pending'
ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
-- dataset 2 for selecting all of the comments made for each ECO in
pending status
SELECT ChangeType.Type, SignoffStages.StageName AS [Stage Name],
ChangeEntry.Number AS [ECO Number], ChangePriority.Priority,
UserProfile.FullName AS [Full Name],
UserProfile.Email, UserProfile_1.FullName AS Initiator,
UserProfile_1.Email AS [Initiator's Email],
ChangeEntry.RaisedOn AS Initiated,
ChangeSignoff.StageActive, ChangeSignoff.StageOrder,
ChangeSignoff.DateSigned, ChangeEntry.Reason,
ChangeEntry.Description, ChangeStatus.Status,
ChangeComments.Comment
FROM ChangeEntry INNER JOIN
ChangePriority ON ChangeEntry.Priority =3D
ChangePriority.ID INNER JOIN
ChangeSignoff ON ChangeEntry.ID =3D
ChangeSignoff.ECOID INNER JOIN
UserProfile ON ChangeSignoff.UserID =3D
UserProfile.ID INNER JOIN
ChangeType ON ChangeEntry.ChangeType =3D
ChangeType.ID INNER JOIN
UserProfile AS UserProfile_1 ON
ChangeEntry.RaisedBy =3D UserProfile_1.ID INNER JOIN
SignoffStages ON ChangeSignoff.StageID =3D
SignoffStages.ID INNER JOIN
ChangeStatus ON ChangeEntry.Status =3D
ChangeStatus.ID INNER JOIN
ChangeComments ON dbo.ChangeSignoff.ECOID =3D
dbo.ChangeComments.ECOID AND ChangeSignoff.UserID =3D
ChangeComments.UserID
WHERE dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D 1
AND
ChangeType.Type like 'CPD%' AND ChangeStatus.Status =3D 'Pending'
ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
Thank you for all of your help in advanceOn Apr 28, 9:26=A0am, Tazo <whitethoma...@.gmail.com> wrote:
> Hi,
> I am trying to create a drilthrough report in report designer. =A0I know
> ho to do it if I have a single query but I do not know how to do it if
> I have multiple. =A0What is happening is that I tried to create a signle
> query where I can then create a groupings and then have the report
> drill through the groupings but while trying to create my query I
> found that I could not create the query that I wanted being that I was
> getting duplicate results in my comments column (I wanted to also add
> the comments). =A0If the user did not make a comment then it was
> automatically added with the first comment made in the for that
> grouping.
> So I decided to make two queries. =A0One is for selecting all of the
> pending items (workflow based system) and the other for all of the
> comments made for each workflow (for this report they are called
> ECO). =A0The queries work just fine if they run seperatly in the report,
> but if I choose to group them so that there is a expand or cllapse
> button under the user then I get the following errors:
> [rsPostSortAggregateInGroupFilterExpression] A grouping filter
> expression for the table =A0table1 =A0uses aggregate function First, Last
> or Previous. =A0These aggregate functions cannot be used in grouping
> filters.
> [rsAggregateInGroupExpression] A group expression for the table
> 'table1' includes an aggregate function. =A0Aggregate functions cannot
> be used in group expressions.
> Build complete -- 2 errors, 0 warnings
> The following are my new datasets:
> -- dataset 1 selecting all of the ECOs that are in pending status
> SELECT =A0dbo.ChangeType.Type, SignoffStages.StageName as [Stage Name],
> dbo.ChangeEntry.Number as [ECO Number],
> =A0 =A0 =A0 =A0 dbo.ChangePriority.Priority, dbo.UserProfile.FullName as [=Full
> Name], dbo.UserProfile.Email, UserProfile_1.FullName AS Initiator,
> =A0 =A0 =A0 =A0 UserProfile_1.Email AS [Initiator's Email],
> dbo.ChangeEntry.RaisedOn as Initiated, dbo.ChangeSignoff.StageActive,
> dbo.ChangeSignoff.StageOrder,
> =A0 =A0 =A0 =A0 dbo.ChangeSignoff.DateSigned, dbo.ChangeEntry.Reason,
> dbo.ChangeEntry.Description, dbo.ChangeStatus.Status
> FROM =A0dbo.ChangeEntry INNER JOIN
> dbo.ChangePriority ON dbo.ChangeEntry.Priority =3D dbo.ChangePriority.ID
> INNER JOIN
> dbo.ChangeSignoff ON dbo.ChangeEntry.ID =3D dbo.ChangeSignoff.ECOID
> INNER JOIN
> dbo.UserProfile ON dbo.ChangeSignoff.UserID =3D dbo.UserProfile.ID INNER
> JOIN
> dbo.ChangeType ON dbo.ChangeEntry.ChangeType =3D dbo.ChangeType.ID INNER
> JOIN
> dbo.UserProfile UserProfile_1 ON dbo.ChangeEntry.RaisedBy =3D
> UserProfile_1.ID INNER JOIN
> dbo.SignoffStages ON ChangeSignoff.StageID =3D SignoffStages.ID INNER
> JOIN
> dbo.ChangeStatus ON dbo.ChangeEntry.Status =3D dbo.ChangeStatus.ID
> WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D =1
> AND ChangeType.Type like '%CPD%' AND ChangeStatus.Status =3D 'Pending'
> ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> -- dataset 2 for selecting all of the comments made for each ECO in
> pending status
> SELECT =A0 =A0 ChangeType.Type, SignoffStages.StageName AS [Stage Name],
> ChangeEntry.Number AS [ECO Number], ChangePriority.Priority,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile.FullName AS [Full =Name],
> UserProfile.Email, UserProfile_1.FullName AS Initiator,
> UserProfile_1.Email AS [Initiator's Email],
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.RaisedOn AS Initia=ted,
> ChangeSignoff.StageActive, ChangeSignoff.StageOrder,
> ChangeSignoff.DateSigned, ChangeEntry.Reason,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.Description, Chang=eStatus.Status,
> ChangeComments.Comment
> FROM =A0 =A0 =A0 =A0 ChangeEntry INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangePriority ON ChangeEntry.=Priority =3D
> ChangePriority.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeSignoff ON ChangeEntry.I=D =3D
> ChangeSignoff.ECOID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile ON ChangeSignoff.U=serID =3D
> UserProfile.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeType ON ChangeEntry.Chan=geType =3D
> ChangeType.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile AS UserProfile_1 O=N
> ChangeEntry.RaisedBy =3D UserProfile_1.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 SignoffStages ON ChangeSignoff=.StageID =3D
> SignoffStages.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeStatus ON ChangeEntry.St=atus =3D
> ChangeStatus.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeComments ON dbo.ChangeSi=gnoff.ECOID =3D
> dbo.ChangeComments.ECOID AND ChangeSignoff.UserID =3D
> ChangeComments.UserID
> WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D =1
> AND
> ChangeType.Type like 'CPD%' AND ChangeStatus.Status =3D 'Pending'
> ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> Thank you for all of your help in advance
just an idea but unsure how to do it
I was wonder if I could use expressions in the second query that will
link each comment to the user
My report design is basically
1st row =3D header
2nd row =3D grouping it just shows the ECO number and when you click on
the + it show the user title, username and the date they signed the
ECO (if they signed it) - This works
3rd row =3D grouping: it shows the comments when you click on the user
cell (+ symbol). right now it will show the first comment on all
because it does not know where they go. This is the second query
I am not sure how I can link thw 3rd row with the user. I figure
maybe I could use an expression but I am not sure how to do it. I am
new to report builder
Thank you for all of your help|||On Apr 28, 9:26=A0am, Tazo <whitethoma...@.gmail.com> wrote:
> Hi,
> I am trying to create a drilthrough report in report designer. =A0I know
> ho to do it if I have a single query but I do not know how to do it if
> I have multiple. =A0What is happening is that I tried to create a signle
> query where I can then create a groupings and then have the report
> drill through the groupings but while trying to create my query I
> found that I could not create the query that I wanted being that I was
> getting duplicate results in my comments column (I wanted to also add
> the comments). =A0If the user did not make a comment then it was
> automatically added with the first comment made in the for that
> grouping.
> So I decided to make two queries. =A0One is for selecting all of the
> pending items (workflow based system) and the other for all of the
> comments made for each workflow (for this report they are called
> ECO). =A0The queries work just fine if they run seperatly in the report,
> but if I choose to group them so that there is a expand or cllapse
> button under the user then I get the following errors:
> [rsPostSortAggregateInGroupFilterExpression] A grouping filter
> expression for the table =A0table1 =A0uses aggregate function First, Last
> or Previous. =A0These aggregate functions cannot be used in grouping
> filters.
> [rsAggregateInGroupExpression] A group expression for the table
> 'table1' includes an aggregate function. =A0Aggregate functions cannot
> be used in group expressions.
> Build complete -- 2 errors, 0 warnings
> The following are my new datasets:
> -- dataset 1 selecting all of the ECOs that are in pending status
> SELECT =A0dbo.ChangeType.Type, SignoffStages.StageName as [Stage Name],
> dbo.ChangeEntry.Number as [ECO Number],
> =A0 =A0 =A0 =A0 dbo.ChangePriority.Priority, dbo.UserProfile.FullName as [=Full
> Name], dbo.UserProfile.Email, UserProfile_1.FullName AS Initiator,
> =A0 =A0 =A0 =A0 UserProfile_1.Email AS [Initiator's Email],
> dbo.ChangeEntry.RaisedOn as Initiated, dbo.ChangeSignoff.StageActive,
> dbo.ChangeSignoff.StageOrder,
> =A0 =A0 =A0 =A0 dbo.ChangeSignoff.DateSigned, dbo.ChangeEntry.Reason,
> dbo.ChangeEntry.Description, dbo.ChangeStatus.Status
> FROM =A0dbo.ChangeEntry INNER JOIN
> dbo.ChangePriority ON dbo.ChangeEntry.Priority =3D dbo.ChangePriority.ID
> INNER JOIN
> dbo.ChangeSignoff ON dbo.ChangeEntry.ID =3D dbo.ChangeSignoff.ECOID
> INNER JOIN
> dbo.UserProfile ON dbo.ChangeSignoff.UserID =3D dbo.UserProfile.ID INNER
> JOIN
> dbo.ChangeType ON dbo.ChangeEntry.ChangeType =3D dbo.ChangeType.ID INNER
> JOIN
> dbo.UserProfile UserProfile_1 ON dbo.ChangeEntry.RaisedBy =3D
> UserProfile_1.ID INNER JOIN
> dbo.SignoffStages ON ChangeSignoff.StageID =3D SignoffStages.ID INNER
> JOIN
> dbo.ChangeStatus ON dbo.ChangeEntry.Status =3D dbo.ChangeStatus.ID
> WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D =1
> AND ChangeType.Type like '%CPD%' AND ChangeStatus.Status =3D 'Pending'
> ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> -- dataset 2 for selecting all of the comments made for each ECO in
> pending status
> SELECT =A0 =A0 ChangeType.Type, SignoffStages.StageName AS [Stage Name],
> ChangeEntry.Number AS [ECO Number], ChangePriority.Priority,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile.FullName AS [Full =Name],
> UserProfile.Email, UserProfile_1.FullName AS Initiator,
> UserProfile_1.Email AS [Initiator's Email],
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.RaisedOn AS Initia=ted,
> ChangeSignoff.StageActive, ChangeSignoff.StageOrder,
> ChangeSignoff.DateSigned, ChangeEntry.Reason,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.Description, Chang=eStatus.Status,
> ChangeComments.Comment
> FROM =A0 =A0 =A0 =A0 ChangeEntry INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangePriority ON ChangeEntry.=Priority =3D
> ChangePriority.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeSignoff ON ChangeEntry.I=D =3D
> ChangeSignoff.ECOID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile ON ChangeSignoff.U=serID =3D
> UserProfile.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeType ON ChangeEntry.Chan=geType =3D
> ChangeType.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile AS UserProfile_1 O=N
> ChangeEntry.RaisedBy =3D UserProfile_1.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 SignoffStages ON ChangeSignoff=.StageID =3D
> SignoffStages.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeStatus ON ChangeEntry.St=atus =3D
> ChangeStatus.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeComments ON dbo.ChangeSi=gnoff.ECOID =3D
> dbo.ChangeComments.ECOID AND ChangeSignoff.UserID =3D
> ChangeComments.UserID
> WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D =1
> AND
> ChangeType.Type like 'CPD%' AND ChangeStatus.Status =3D 'Pending'
> ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> Thank you for all of your help in advance
I was wonder if I could use expressions in the second query that will
link each comment to the user
My report design is basically
1st row =3D header
2nd row =3D grouping it just shows the ECO number and when you click on
the + it show the user title, username and the date they signed the
ECO (if they signed it) - This works
3rd row =3D grouping: it shows the comments when you click on the user
cell (+ symbol). right now it will show the first comment on all
because it does not know where they go. This is the second query
I am not sure how I can link thw 3rd row with the user. I figure
maybe I could use an expression but I am not sure how to do it. I am
new to report builder
Thank you for all of your help|||On Apr 28, 10:36=A0am, Tazo <whitethoma...@.gmail.com> wrote:
> On Apr 28, 9:26=A0am, Tazo <whitethoma...@.gmail.com> wrote:
>
>
> > Hi,
> > I am trying to create a drilthrough report in report designer. =A0I know=
> > ho to do it if I have a single query but I do not know how to do it if
> > I have multiple. =A0What is happening is that I tried to create a signle=
> > query where I can then create a groupings and then have the report
> > drill through the groupings but while trying to create my query I
> > found that I could not create the query that I wanted being that I was
> > getting duplicate results in my comments column (I wanted to also add
> > the comments). =A0If the user did not make a comment then it was
> > automatically added with the first comment made in the for that
> > grouping.
> > So I decided to make two queries. =A0One is for selecting all of the
> > pending items (workflow based system) and the other for all of the
> > comments made for each workflow (for this report they are called
> > ECO). =A0The queries work just fine if they run seperatly in the report,=
> > but if I choose to group them so that there is a expand or cllapse
> > button under the user then I get the following errors:
> > [rsPostSortAggregateInGroupFilterExpression] A grouping filter
> > expression for the table =A0table1 =A0uses aggregate function First, Las=t
> > or Previous. =A0These aggregate functions cannot be used in grouping
> > filters.
> > [rsAggregateInGroupExpression] A group expression for the table
> > 'table1' includes an aggregate function. =A0Aggregate functions cannot
> > be used in group expressions.
> > Build complete -- 2 errors, 0 warnings
> > The following are my new datasets:
> > -- dataset 1 selecting all of the ECOs that are in pending status
> > SELECT =A0dbo.ChangeType.Type, SignoffStages.StageName as [Stage Name],
> > dbo.ChangeEntry.Number as [ECO Number],
> > =A0 =A0 =A0 =A0 dbo.ChangePriority.Priority, dbo.UserProfile.FullName as= [Full
> > Name], dbo.UserProfile.Email, UserProfile_1.FullName AS Initiator,
> > =A0 =A0 =A0 =A0 UserProfile_1.Email AS [Initiator's Email],
> > dbo.ChangeEntry.RaisedOn as Initiated, dbo.ChangeSignoff.StageActive,
> > dbo.ChangeSignoff.StageOrder,
> > =A0 =A0 =A0 =A0 dbo.ChangeSignoff.DateSigned, dbo.ChangeEntry.Reason,
> > dbo.ChangeEntry.Description, dbo.ChangeStatus.Status
> > FROM =A0dbo.ChangeEntry INNER JOIN
> > dbo.ChangePriority ON dbo.ChangeEntry.Priority =3D dbo.ChangePriority.ID=
> > INNER JOIN
> > dbo.ChangeSignoff ON dbo.ChangeEntry.ID =3D dbo.ChangeSignoff.ECOID
> > INNER JOIN
> > dbo.UserProfile ON dbo.ChangeSignoff.UserID =3D dbo.UserProfile.ID INNER=
> > JOIN
> > dbo.ChangeType ON dbo.ChangeEntry.ChangeType =3D dbo.ChangeType.ID INNER=
> > JOIN
> > dbo.UserProfile UserProfile_1 ON dbo.ChangeEntry.RaisedBy =3D
> > UserProfile_1.ID INNER JOIN
> > dbo.SignoffStages ON ChangeSignoff.StageID =3D SignoffStages.ID INNER
> > JOIN
> > dbo.ChangeStatus ON dbo.ChangeEntry.Status =3D dbo.ChangeStatus.ID
> > WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required ==3D 1
> > AND ChangeType.Type like '%CPD%' AND ChangeStatus.Status =3D 'Pending'
> > ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> > -- dataset 2 for selecting all of the comments made for each ECO in
> > pending status
> > SELECT =A0 =A0 ChangeType.Type, SignoffStages.StageName AS [Stage Name],=
> > ChangeEntry.Number AS [ECO Number], ChangePriority.Priority,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile.FullName AS [Ful=l Name],
> > UserProfile.Email, UserProfile_1.FullName AS Initiator,
> > UserProfile_1.Email AS [Initiator's Email],
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.RaisedOn AS Init=iated,
> > ChangeSignoff.StageActive, ChangeSignoff.StageOrder,
> > ChangeSignoff.DateSigned, ChangeEntry.Reason,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.Description, Cha=ngeStatus.Status,
> > ChangeComments.Comment
> > FROM =A0 =A0 =A0 =A0 ChangeEntry INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangePriority ON ChangeEntr=y.Priority =3D
> > ChangePriority.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeSignoff ON ChangeEntry=.ID =3D
> > ChangeSignoff.ECOID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile ON ChangeSignoff=.UserID =3D
> > UserProfile.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeType ON ChangeEntry.Ch=angeType =3D
> > ChangeType.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile AS UserProfile_1= ON
> > ChangeEntry.RaisedBy =3D UserProfile_1.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 SignoffStages ON ChangeSigno=ff.StageID =3D
> > SignoffStages.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeStatus ON ChangeEntry.=Status =3D
> > ChangeStatus.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeComments ON dbo.Change=Signoff.ECOID =3D
> > dbo.ChangeComments.ECOID AND ChangeSignoff.UserID =3D
> > ChangeComments.UserID
> > WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required ==3D 1
> > AND
> > ChangeType.Type like 'CPD%' AND ChangeStatus.Status =3D 'Pending'
> > ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> > Thank you for all of your help in advance
> just an idea but unsure how to do it
> I was wonder if I could use expressions in the second query that will
> link each comment to the user
> My report design is basically
> 1st row =3D header
> 2nd row =3D grouping it just shows the ECO number and when you click on
> the + it show the user title, username and the date they signed the
> ECO (if they signed it) - This works
> 3rd row =3D =A0grouping: it shows the comments when you click on the user
> cell (+ symbol). right now it will show the first comment on all
> because it does not know where they go. This is the second query
> I am not sure how I can link thw 3rd row with the user. =A0I figure
> maybe I could use an expression but I am not sure how to do it. =A0I am
> new to report builder
> Thank you for all of your help- Hide quoted text -
> - Show quoted text -
OK, well I figured it out......
I figured out that you cannot have a report utilize the different
datasets and link them together in a grouping (creating a drillthough
report). Instead what you need to do is create another report that
utilizes the same datasource or a datasource that has data that can
relate the datasource on the main report and set parameters in it. In
the layout view just use textboxes and drag the operated field. From
there you go back to the main report and drag the subreport control in
one of the cells and click properties. Select the second report that
you created and then click the parameters tab. In the parameter name
section use the dropdown menu and the select the parameters (the
dropdown menu will auto fill with the parameters from the subreport).
In the parameters value field select the column that you want to pass
to the subreport.
=46rom there is it all the same for creating a drillthough report
Everything should work out perfectly; although there migh be another
way.
Thank You

Help with sproc with one parameter that can contain multiple values

I am trying to get the following procedure to work and I am getting hung up
on the @.strClaim parameter, this could be either 1 or more claim numbers
for one terminal number. I want to be able to get all the claim detail
information for, say, terminal # 1222222abc that are in claims 521, 522,
523, 530.
I don't know how to handle the @.strClaim so that the procedure will for all
claim numbers in that list.
Any help appreciated.
TIA
Nancy
Create Procedure usp_GetClaims
(@.strClaim as Char(10),
@.strTerminal as Char(30))
as
Select X_CLAIMS_NO,X_TERMINAL_NUMBER
from
dbo.X_HCFA_CLAIM
where
Cast(X_CLAIMS_NO as char(10)) IN @.strClaim
AND
X_TERMINAL_NUMBER = @.strTerminal
exec usp_GetClaims '574, 573', 'RMFAHESSSXYHLLLX'To pass a CSV list as a VARCHAR(n) parameter, you will have to use something
different. For various alternatives, refer to:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||Thanks, I think I found what I needed. Great site too!
Nancy
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OVgTMF1oFHA.3316@.tk2msftngp13.phx.gbl...
> To pass a CSV list as a VARCHAR(n) parameter, you will have to use
> something different. For various alternatives, refer to:
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Anith
>

Friday, March 23, 2012

Help with search code

I have been using the following code on a search page for some time, is has worked very well. We recently changed our database to support multiple addresses for each client. So I added the INNER JOIN on the tblClientAddresses. But now when I try to search on a ID I get an ambiguous cloumn name error on the ID. Can anyone see how I could correct this?

Thanks for any suggestions,


Sub BindDataForPaging(ByVal sortExpr As String)
 Dim MyConnection As SqlConnection
 Dim MySQLAdapter As SqlDataAdapter
 Dim DS As DataSet
 Dim ConnectStr As String
 Dim WhereClause As String
 Dim SelectStatement As String If SearchLastName.Text = "" And SearchFirstName.Text = "" And _
 SearchID.Text = "" And SearchCompanyName.Text = "" And _
 SearchSal1.Text = "" And SearchComment.Text = "" And SearchAddress.Text = "" Then
 Message.Text = "You didn't enter any search parameters. Try Again."
 Exit Sub
 End If
 WhereClause = "Where "
 If SearchLastName.Text <> "" Then
 WhereClause = WhereClause & "[LastName] Like '" & _
 SearchLastName.Text & "%" & "' AND "
 End If
 If SearchFirstName.Text <> "" Then
 WhereClause = WhereClause & "[FirstName] Like '" & _
 SearchFirstName.Text & "%" & "' AND "
 End If
 If SearchID.Text <> "" Then
 WhereClause = WhereClause & "[ID] = " & _
 SearchID.Text & " AND "
 End If
 If SearchCompanyName.Text <> "" Then
 WhereClause = WhereClause & "[CompanyName] Like '" & _
 SearchCompanyName.Text & "%" & "' AND "
 End If
 If SearchSal1.Text <> "" Then
 WhereClause = WhereClause & "[Sal1] Like '" & _
 SearchSal1.Text & "%" & "' AND "
 End If
 If SearchComment.Text <> "" Then
 WhereClause = WhereClause & "[Comments] Like '" & "%" & _
 SearchComment.Text & "%" & "' AND "
 End If
 If SearchAddress.Text <> "" Then
 WhereClause = WhereClause & "[Address] Like '" & "%" & _
 SearchAddress.Text & "%" & "' AND "
 End If
 If ClientTypeDrop.SelectedItem.Text <> "" Then
 WhereClause = WhereClause & "[CLientType] Like '" & "%" & _
 ClientTypeDrop.SelectedItem.Text & "%" & "' AND "
 End If
 If Right(WhereClause, 4) = "AND " Then
 WhereClause = Left(WhereClause, Len(WhereClause) - 4)
 End If
 SelectStatement = "Select *,A.Address FROM tblClients INNER JOIN dbo.tblClientAddresses A ON dbo.tblClients.ID = A.ID " & WhereClause & " ORDER BY " & sortExpr
 Try
 ConnectStr = ConfigurationSettings.AppSettings("ConnectStr")
 MyConnection = New SqlConnection(ConnectStr)
 MySQLAdapter = New SqlDataAdapter(SelectStatement, MyConnection)
 DS = New DataSet
 MySQLAdapter.Fill(DS)
 MyDataGrid.DataSource = DS
 MyDataGrid.DataBind()
 Catch objException As SqlException
 Dim objError As SqlError
 For Each objError In objException.Errors
 Response.Write(objError.Message)
 Next
 End Try
 End Sub

WhereClause = WhereClause & "[ID] = " & _

Which tables ID do you mean?|||I tried both as below and got a runtime error, but it is the ID from tblClients that I would like.

WhereClause = WhereClause & "tblClients.[ID] = " & _

Thank you,|||That's exactly what you need to do. You're still getting an error - and its still the same error? Can you just print out the resulting query rather than all the string concats?|||Try this instead:


WhereClause = WhereClause & "dbo.tblClients.[ID] = " & _

If this doesn't work, we need to see what SelectStatement contains exactly. Plus an exact error message would be helpful.

Terrisql

Monday, March 19, 2012

Help with Query - Insert multiple rows and link between tables.

I am trying to do the following:

Insertn rows into A Table calledEAItems. For each row that is inserted intoEAItemsI need to take thatItemID(PK) and insert a row intoEAPackageItems.

I'm inserting rows from a Table calledEATemplateItems.

So far I have something like this: (I have the PackageID already at the start of the query).

 INSERT INTO EAItems(Description, Recommendation, HeadingID)
SELECT Description, Recommendation, HeadingID
FROM EATemplateItemsWHERE EATemplateItems.TemplateID = @.TemplateIDINSERT INTO EAPackageItems(ItemID, PackageID) ...

I have no idea how to grab each ITemID as it's created, and then put it into the EAPackageItems right away.Any Advice / help would rock! Thanks

I think you will want to do this as a stored procedure. As you insert an individual row you can use the @.@.IDENTITY variable for the last inserted row. You could save that to a variable and insert the record to the second table. In your first query you could adjust it to select the rows into a table variable and then loop over the rows in the table variable and use that loop to take care of your individual inserts.

The T-SQL snippet below is the basic structure for what I am describing.

DECLARE @.MyTableTABLE(IDint IDENTITY,Name varchar(20))INSERT INTO @.MyTable (Name)SELECT NameFROM OtherTableDECLARE @.CurIDintDECLARE @.MaxIDintDECLARE @.RowIDintSET @.MaxID = (SELECT MAX(ID)FROM @.MyTable )SET @.CurID = 1WHILE (@.CurID <= @.MaxID)BEGIN-- use CurID to access the row in @.MyTable-- do your insert-- get the @.@.IDENTITY-- use that value for the next insert-- be sure to increment the @.CurID to the next rowSET @.CurID = @.CurID + 1END

|||

Thanks for the reply.

I'll work with that when I get to work - it seems logically straight forward. The script you put down can work in both SQL 2000 and SQL 2005 right? I hope so :D

|||Yes, there is nothing specific in there for SQL Server 2005.

Monday, March 12, 2012

Help with query

I'm not sure if this is the right forum but here goes:

I want to make a query that selects data from multiple tables and joins it all together. I have that but what i want to do is only select the data I need. at the moment it is returning columns that are not necessary. I'm trying to do something like this:

Code Snippet

SELECT
sysdba.OPPORTUNITY.OPPORTUNITYID AS OPPID
FROM sysdba.OPPORTUNITY
INNER JOIN sysdba.C_OPPTYINFO
ON sysdba.OPPORTUNITY.OPPORTUNITYID = sysdba.C_OPPTYINFO.OPPORTUNITYID

For some reason the Inner Join is not joining the two tables. Any one have any suggestions?

Thanks in advance for the help.

Nothing wrong with your query, the tables are properly joined -IF both have a column [OpportunityID].

However, are you sure that there is data in both tables with the exact same [OpportunityID]?

|||The limited information you gave makes it hard for us to answer your questions: Perhaps the OppurtunityId is not the only key needed to identify the matching rows in both tables ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Monday, February 27, 2012

Help with multiple Left Joins

Hi All,
this is my first time posting here as i cannot find the answer myself.
I have couple tables i want to join and i can't seem to get it right. I
have the following tables:
Part: (Part ID), PartDescription
Part_warehouse: ( WarehouseID), (Part_ID), Available_QTY
Inventory_Trans: (Transaction_ID), PartID, QTY, TYPE
I want a query of the Available qty >0 for every part we have. When i
do a query like this, i get 5363 records.
SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
dbo.PART_WAREHOUSE.AVAILABLE_QTY
FROM dbo.PART left outer JOIN
dbo.PART_WAREHOUSE ON dbo.PART.ID =
dbo.PART_WAREHOUSE.PART_ID
WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
available_qty, part.unit_material_cost
Then i want to add a column for this query, the inventory_Trans.Qty
that has type =O. I tried the query below and it doesn't
work...obviously ican't inner join again from PART_WAREHOUSE as it
does the left join based on that table, so this wouldn't work:
SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
dbo.PART_WAREHOUSE.AVAILABLE_QTY
FROM dbo.PART
left outer JOIN dbo.PART_WAREHOUSE ON dbo.PART.ID =
dbo.PART_WAREHOUSE.PART_IS
left outer JOIN dbo.INVENTORY_TRANS ON dbo.PART_WAREHOUSE.PART_ID =
dbo.INVENTORY_TRANS.PART_ID
WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)AND
(INVENTORY_TRANS.TYPE='O')
group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
available_qty, part.unit_material_cost
I tried using this, but i am not familiar with this syntax and i am
getting errors.
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
FROM PART p1, PART p2
LEFT JOIN
dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
LEFT JOIN
dbo.INVENTORY_TRANS as i on p2.ID =
dbo.INVENTORY_TRANS.PART_ID
WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
group by w.WAREHOUSE_ID, p1.ID, p1.DESCRIPTION, w.available_qty,
p1.unit_material_cost
Order by p1.warehouse_Id
so i am out of ideas. Can anyone enlighten me about how to do this:'
thank you so much in advance.Although this probably isn't the answer that you are looking for, but I'm
wondering why you are using the GROUP BY clause in your query? You typically
use GROUP BY when using an aggregate function in the SELECT statement, such
as COUNT. Try running the second and third queries without the GROUP BY
clause.
Try
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
FROM PART p1
LEFT JOIN dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
LEFT JOIN dbo.INVENTORY_TRANS as i on w.ID = i.PART_ID
WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
Order by p1.warehouse_Id
"lytung@.gmail.com" wrote:

> Hi All,
> this is my first time posting here as i cannot find the answer myself.
> I have couple tables i want to join and i can't seem to get it right. I
> have the following tables:
> Part: (Part ID), PartDescription
> Part_warehouse: ( WarehouseID), (Part_ID), Available_QTY
> Inventory_Trans: (Transaction_ID), PartID, QTY, TYPE
> I want a query of the Available qty >0 for every part we have. When i
> do a query like this, i get 5363 records.
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART left outer JOIN
> dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
>
> Then i want to add a column for this query, the inventory_Trans.Qty
> that has type =O. I tried the query below and it doesn't
> work...obviously ican't inner join again from PART_WAREHOUSE as it
> does the left join based on that table, so this wouldn't work:
>
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART
> left outer JOIN dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_IS
> left outer JOIN dbo.INVENTORY_TRANS ON dbo.PART_WAREHOUSE.PART_ID =
> dbo.INVENTORY_TRANS.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)AND
> (INVENTORY_TRANS.TYPE='O')
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
> I tried using this, but i am not familiar with this syntax and i am
> getting errors.
> SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
> w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
> FROM PART p1, PART p2
> LEFT JOIN
> dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
> LEFT JOIN
> dbo.INVENTORY_TRANS as i on p2.ID =
> dbo.INVENTORY_TRANS.PART_ID
> WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
> group by w.WAREHOUSE_ID, p1.ID, p1.DESCRIPTION, w.available_qty,
> p1.unit_material_cost
> Order by p1.warehouse_Id
>
> so i am out of ideas. Can anyone enlighten me about how to do this:'
> thank you so much in advance.
>|||no that gave me an error.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'warehouse_Id'.
But even if that wo rked the logic doens't make sense.
I want the second query to be based on the first query. Maybe its not
about doing two joins but what i probably need is a transaction query.
First i need this:
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, w.WAREHOUSE_ID
FROM PART p1
LEFT JOIN dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
where ( w.AVAILABLE_QTY > 0)
Then i need the i.type='O' (from inventory_trans) based on those
results. I hope this make sense!|||Hi
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART left outer JOIN
> dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
is it possible that part with given ID doesn't belong to a part_warehouse?
in other words can you get null as warehouse_id in above query?
the second thing - group by clause here is really not necessary

> Then i want to add a column for this query, the inventory_Trans.Qty
> that has type =O. I tried the query below and it doesn't
> work...obviously ican't inner join again from PART_WAREHOUSE as it
> does the left join based on that table, so this wouldn't work:
how about this?
SELECT pw.WAREHOUSE_ID, p.ID, p.DESCRIPTION, p.UNIT_MATERIAL_COST,
pw.AVAILABLE_QTY
FROM dbo.PART p left outer JOIN
( dbo.PART_WAREHOUSE pw inner join dbo.INVENTORY_TRANS itr ON pw.PART_ID =
itr.PART_ID
) ON p.ID = pw.PART_ID
WHERE (pw.AVAILABLE_QTY > 0)
AND (itr.TYPE='O')
HTH
Peter|||Hi Peter,
thanks for replying. The query you gave me ended up with too many
records. You are right, i dont need the group by statement.
Part_Warehouse has 2 Primary Keys: Part_ID, and WAREHOUSE_ID
you skipped out the PART_WAREHOUSE join to PART. I guess for this join
it doesn't have to be a left join, but it has to be joined. The second
join has to be left, which you did...
I am getting

with mixing the joins. What is the general rule
of multiple joins? does the second join depend on the previous join? or
can they be independent?|||use parentheses to prioritize joins. the outer table is joined to result of
join in parentheses.
can you show the ddl of these tables and some sample data and describe
result you would like to obtain?
part_warehouse is a table that relates parts and warehouses?
> you skipped out the PART_WAREHOUSE join to PART. I guess for this join
> it doesn't have to be a left join, but it has to be joined. The second
> join has to be left, which you did...
FROM dbo.PART p left outer JOIN
( dbo.PART_WAREHOUSE pw inner join dbo.INVENTORY_TRANS itr ON pw.PART_ID =
itr.PART_ID
) ON p.ID = pw.PART_ID
no, I left joined PART to the result of inner join between PART_WAREHOUSE
and INVENTORY_TRANS.
again, do you have PARTs without WAREHOUSEs?
peter

Help with multiple jobs failing

Hello,
For some reason a couple of our jobs have been failing lately. We get
the following msg on the details of the job. Any ideas? Thanks in advance.
Msg:
Unable to connect to SQL Server 'COMPUTERNAME\INSTANCENAME'. The step
failed.
1. Is this the same server where the jobs are running?
2. Have you checked your SQL Agent logs for any more information on these
error messages?
3. Can you connect to this named isntance using Query analyser, SEM, etc?
4. Was this sql server "moved" from another box, or any such thing?
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Vikram,
1. Yes
2. See below output
3. Yes
4. We did upgrade the sql box a couple of months ago. We created scripts
and ran them against the new instance. The seem to be ran fine from Query
analyser. It's not the same job that fails everytime either it seems to be
random.
jake
2004-06-16 08:19:24 - ? [393] Waiting for SQL Server to recover databases...
2004-06-16 08:21:22 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID
2004-06-16 08:21:23 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID 1596
2004-06-16 08:21:23 - ? [101] SQL Server computername\DBSERVER version
8.00.760 (0 connection limit)
2004-06-16 08:21:23 - ? [102] SQL Server ODBC driver version 3.85.1025
2004-06-16 08:21:23 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
Local host server is computername\DBSERVER
2004-06-16 08:21:23 - ? [310] 1 processor(s) and 992 MB RAM detected
2004-06-16 08:21:23 - ? [339] Local computer is computername running Windows
NT 5.2 (3790)
2004-06-16 08:21:23 - ! [364] The Messenger service has not been started -
NetSend notifications will not be sent
2004-06-16 08:21:23 - ? [129] SQLAgent$DBSERVER starting under Windows NT
service control
2004-06-16 08:21:23 - ? [392] Using MAPI32.DLL from C:\WINDOWS\SYSTEM32
(version 1.0.2536.0)
2004-06-16 08:21:23 - ? [196] Attempting to start mail session using profile
'Outlook'...
2004-06-16 08:21:25 - ? [353] Mail session started (using MAPI1)
2004-06-16 08:21:25 - + [396] An idle CPU condition has not been defined -
OnIdle job schedules will have no effect
2004-06-17 05:19:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-17 05:19:32 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-18 02:04:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-18 05:21:14 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-18 05:21:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-19 05:13:16 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-20 05:15:57 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-20 05:15:57 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-20 05:20:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 02:14:18 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 02:14:18 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-21 05:16:45 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 05:16:45 - ! [382] Logon to server 'computername\DBSERVER' failed
(SaveAllSchedules)
2004-06-21 05:17:53 - ! [298] SQLServer Error: 17, SQL Server does not exist
or access denied. [SQLSTATE 08001]
2004-06-21 05:17:53 - ! [298] SQLServer Error: 53, ConnectionOpen
(Connect()). [SQLSTATE 01000]
2004-06-21 08:04:08 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 08:04:08 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-22 02:18:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:18:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:20:39 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:20:39 - ! [382] Logon to server 'computername\DBSERVER' failed
(SaveAllSchedules)
2004-06-22 07:01:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 02:17:10 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 02:17:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-23 02:19:15 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 07:30:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 08:19:30 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 08:19:30 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-24 05:15:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-24 05:15:41 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-24 05:16:53 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-24 05:16:53 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:08:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-25 02:08:23 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:09:27 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-25 02:09:32 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:18:07 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:27:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:29:40 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:29:40 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-28 02:03:04 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-28 05:16:25 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-28 08:50:06 - ? [131] SQLAgent$DBSERVER service stopping due to a
stop request from a user, process, or the OS...
2004-06-28 08:50:07 - ? [358] Mail session ended
2004-06-28 08:50:09 - ? [098] SQLServerAgent terminated (normally)
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:N8GBxNPXEHA.328@.cpmsftngxa10.phx.gbl...
> 1. Is this the same server where the jobs are running?
> 2. Have you checked your SQL Agent logs for any more information on these
> error messages?
> 3. Can you connect to this named isntance using Query analyser, SEM, etc?
> 4. Was this sql server "moved" from another box, or any such thing?
> Thanks,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
|||Vikram,
1. Yes
2. See below output
3. Yes
4. We did upgrade the sql box a couple of months ago. We created scripts
and ran them against the new instance. The seem to be ran fine from Query
analyser. It's not the same job that fails everytime either it seems to be
random.
jake
2004-06-16 08:19:24 - ? [393] Waiting for SQL Server to recover databases...
2004-06-16 08:21:22 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID
2004-06-16 08:21:23 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID 1596
2004-06-16 08:21:23 - ? [101] SQL Server computername\DBSERVER version
8.00.760 (0 connection limit)
2004-06-16 08:21:23 - ? [102] SQL Server ODBC driver version 3.85.1025
2004-06-16 08:21:23 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
Local host server is computername\DBSERVER
2004-06-16 08:21:23 - ? [310] 1 processor(s) and 992 MB RAM detected
2004-06-16 08:21:23 - ? [339] Local computer is computername running Windows
NT 5.2 (3790)
2004-06-16 08:21:23 - ! [364] The Messenger service has not been started -
NetSend notifications will not be sent
2004-06-16 08:21:23 - ? [129] SQLAgent$DBSERVER starting under Windows NT
service control
2004-06-16 08:21:23 - ? [392] Using MAPI32.DLL from C:\WINDOWS\SYSTEM32
(version 1.0.2536.0)
2004-06-16 08:21:23 - ? [196] Attempting to start mail session using profile
'Outlook'...
2004-06-16 08:21:25 - ? [353] Mail session started (using MAPI1)
2004-06-16 08:21:25 - + [396] An idle CPU condition has not been defined -
OnIdle job schedules will have no effect
2004-06-17 05:19:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-17 05:19:32 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-18 02:04:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-18 05:21:14 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-18 05:21:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-19 05:13:16 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-20 05:15:57 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-20 05:15:57 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-20 05:20:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 02:14:18 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 02:14:18 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-21 05:16:45 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 05:16:45 - ! [382] Logon to server 'computername\DBSERVER' failed
(SaveAllSchedules)
2004-06-21 05:17:53 - ! [298] SQLServer Error: 17, SQL Server does not exist
or access denied. [SQLSTATE 08001]
2004-06-21 05:17:53 - ! [298] SQLServer Error: 53, ConnectionOpen
(Connect()). [SQLSTATE 01000]
2004-06-21 08:04:08 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 08:04:08 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-22 02:18:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:18:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:20:39 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:20:39 - ! [382] Logon to server 'computername\DBSERVER' failed
(SaveAllSchedules)
2004-06-22 07:01:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 02:17:10 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 02:17:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-23 02:19:15 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 07:30:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 08:19:30 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 08:19:30 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-24 05:15:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-24 05:15:41 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-24 05:16:53 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-24 05:16:53 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:08:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-25 02:08:23 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:09:27 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-25 02:09:32 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:18:07 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:27:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:29:40 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:29:40 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-28 02:03:04 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-28 05:16:25 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-28 08:50:06 - ? [131] SQLAgent$DBSERVER service stopping due to a
stop request from a user, process, or the OS...
2004-06-28 08:50:07 - ? [358] Mail session ended
2004-06-28 08:50:09 - ? [098] SQLServerAgent terminated (normally)
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:N8GBxNPXEHA.328@.cpmsftngxa10.phx.gbl...
> 1. Is this the same server where the jobs are running?
> 2. Have you checked your SQL Agent logs for any more information on these
> error messages?
> 3. Can you connect to this named isntance using Query analyser, SEM, etc?
> 4. Was this sql server "moved" from another box, or any such thing?
> Thanks,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Help with multiple jobs failing

Help with Multiple inserts

HI,
How do I rewrite the first query using the datafrom query 2 so that I
can do multipe inserts for all the units given this input:
@.EnrolmentID,
@.dteEnroled,
@.Outcome,
@.CourseID
-- 1. This creates one unit enrolment using parameters
INSERT INTO tblUnitEnrolment (EnrolmentID,EnrolDate,Outcome,
QualUnitID) VALUES (@.EnrolmentID,@.dteEnroled,@.Outcome, @.QualUnitID)
-- 2. This gets a list of Unit ID's for a given Course
SELECT QualUnitID FROM QualUnits WHERE QualID=(SELECT QualID FROM
COURSES WHERE CourseID=@.intCourse)Hi
I'm not sure understand your question
INSERT INTO tblUnitEnrolment (EnrolmentID,EnrolDate,Outcome,
QualUnitID) SELECT @.EnrolmentID,@.dteEnroled,@.Outcome, QualUnitID FROM
QualUnits WHERE QualID=(SELECT QualID FROM
COURSES WHERE CourseID=@.intCourse)
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1130322728.990452.313140@.g43g2000cwa.googlegroups.com...
> HI,
> How do I rewrite the first query using the datafrom query 2 so that I
> can do multipe inserts for all the units given this input:
> @.EnrolmentID,
> @.dteEnroled,
> @.Outcome,
> @.CourseID
>
> -- 1. This creates one unit enrolment using parameters
> INSERT INTO tblUnitEnrolment (EnrolmentID,EnrolDate,Outcome,
> QualUnitID) VALUES (@.EnrolmentID,@.dteEnroled,@.Outcome, @.QualUnitID)
> -- 2. This gets a list of Unit ID's for a given Course
> SELECT QualUnitID FROM QualUnits WHERE QualID=(SELECT QualID FROM
> COURSES WHERE CourseID=@.intCourse)
>|||Thanks - that does the trick

Help with multiple IIFs, or need suggestion of better solution.

I am trying to check multiple fields from a db to see if they have either a 1 or 0 value, and if there is a 1, then write a value into a text box. I need to check multiple fields, and if all of them are checked then I have to insert the value for each into the text box. If it was just checking one condition it woudl be easy, because I could just nest IIF's until it was true.

So I can't do because once the truth clause is satisfied it will exit the loop: IIF(Fields!Fielda.Value = 1,"Fielda",IIF(Fields!Fieldb.Value=1,"Fieldb"....)

I also cannot do:
=IIfFields!Fielda.Value=1,"Fielda,"")
=IifFields!Fieldb.Value=1,"Fieldb,"")

Is there a way to have a whole bunch of IIF's, or can anyone think of another way to do this?

Much appreciated.

Use the "And" operator. It would look like this:

iif (Fields!Fielda.Value = 1 and Fields!Fieldb.Value=1 and Fields!Fieldc.Value=1, "Fielda", "")

|||Ryan, I appreciate the answer, but I think you misunderstood. I want it to say if Fielda = 1 then insert text, and if Fieldb = 1 then insert text, not if all of them = 1.

This would be the ideal situation:

=IIF(Fields!Fielda.Value=1,"Fielda","")
IIF(Fields!Fieldb.Value=1,"Fieldb","")
IIF(Fields!Fieldc.Value=1,"Fieldc","")
And so on for all the fields for this particular text box.

Or another example (that I've tried that did not work)
=IIF(Fields!Fielda.Value=1,"Fielda","") &
IIF(Fields!Fieldb.Value=1,"Fieldb","") &
IIF(Fields!Fieldc.Value=1,"Fieldc","") &

I can't use what you said because that would only evaluate one statement, and I need to evaluate 8 different statements. That's the problem. Is there a way to have mutliple seperate IIF's in an expression like I have above? If not, is there another solution?
|||

One question is what is the datatype on the database field? If it is boolean then you should be able to do:

=IIF(Fields!Fielda.Value,"Fielda","") + IIF(Fields!Fieldb.Value,"Fieldb","")...

The + should work for concatenation since all of the fields area string. Another thing that I have seen is that you may have to do CDec on the database fields to force a datatype match.

=IIF(CDec(Fields!Fielda.Value)=1,"Fielda","") + IIF(CDec(Fields!Fieldb.Value)=1,"Fieldb","") ...

|||

Use the Report Properties.Code.Custom Code feature.

1.Create a function in the CODE section

2. Pass all your field values to the function

3. The return value is used in the textbox.

You have a lot more coding power in the CODE section than you do with expressions.

Hope this helps.

|||

Can you do it in SQL using case statement ?

|||

=switch(Fields!FieldA.Value = 1, "A", Fields!FieldB.Value = 1, "B", true, "")

Thanks, Donovan.

Help with multiple counts..

I have a application table that I have turned into a cube. It includes attributes of a organizaiton name, month, and year. This all works fine.

I would like to add another table to the cube that counts a visits. It also has attributes of organization name, month and year.

I am confused where I make the associations so they can operate in the same cube.

Do I join the tables in the datasource and just add the measure in the cube? Do I add a new dimention in the cube?

Any help in this area would be great. I am basically looking for a cube like so..

Month Year

Organization Name ApplicationCount (From the application table)

Visit Count (From the visit table)

Thanks in advance,

Mardo

If it has a similar structure, just a different measure, then you could add this table as a separate measure group with a measure in it that maps to the Visit Count. If these two table are completely identical you might be able to join them with a view or a named query in the DSV so that Visit Count is just added as an extra column to the existing cube, this might be a more efficient approach.

Help with Multiple connections in a CLR stored procedure

Here's what I'm trying to accomplish:

1. Open a connection and retrieve a datareader, using the context connection.
2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection.

The problem I have is that I can't open the second connection. Here's some sample code:

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure]
public static void up_TestClr() {

//Use the current context connection
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT TOP 100 AccountId FROM Account";

conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {

SqlConnection conn2 = new SqlConnection();
conn2.ConnectionString = "Server=localhost;Database=TestDb;Integrated Security=SSPI";
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = conn;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "up_TestProc";
int serviceFilterId = Convert.ToInt32(reader["accountId"]);
SqlParameter parm = new SqlParameter("@.accountId", serviceFilterId);
cmd2.Parameters.Add(parm);
conn2.Open();
cmd2.ExecuteNonQuery();
cmd2.Dispose();
}

reader.Close();
conn.Close();

}

};

When I attempt to execute the procedure, I get the following:

Msg 6549, Level 16, State 1, Procedure up_TestClr, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'up_TestClr':

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.SqlClient.SqlConnection.PermissionDemand()

at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at StoredProcedures.up_TestClr(String& dbName)

. User transaction, if any, will be rolled back.
Any suggestions? I've tried unsuccessfully to create an Asymetric key to mark my assembly for External Access:

USE master

CREATE ASYMMETRIC KEY SN FROM EXECUTABLE FILE = 'C:\Shc\SqlServerProject1.dll'

CREATE LOGIN TestLogin FROM ASYMMETRIC KEY SN

GRANT EXTERNAL ACCESS ASSEMBLY TO TestLogin

Resulted in:

Msg 15208, Level 16, State 1, Line 2
The certificate, asymmetric key, or private key file does not exist or has invalid format.
Msg 15151, Level 16, State 1, Line 3
Cannot find the asymmetric key 'SN', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 4
Cannot find the login 'TestLogin', because it does not exist or you do not have permission.
ANY IDEAS/SUGGESTIONS?

<AKS@.discussions.microsoft.com> wrote in message news:68f11211-89fe-4a3f-972c-512cddfc8ec4@.discussions.microsoft.com... 1. Open a connection and retrieve a datareader, using the context connection.2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection. Unfortunately, you can only have a single context connection open at once. Have you considered using a DataSet instead of the DataReader? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--|||Ugh. I didn't realize that I could use a DataSet inside a CLR-based SP. I guess somehow, all the examples I've seen used the SqlDataReader.

That should work fine. Thx.|||

Not sure how using a DataSet helps, but...

He is not opening a 2nd context connection. He is opening a 2nd connection that may happen to point to the same server/database as the context connection.

This may be needed for example to persist auditing information that needs to survive even if the context connection's transaction is later rolled back.

Are you saying this is not allowed?

My code attempts to create a 2nd connection like this, and I'm getting the same error when I attempt to .Open() it later:

<code>

public static SqlConnection NewConnection()

{

SqlConnection connection2 = new SqlConnection("context connection=true");

connection2.Open();

SqlCommand command = new SqlCommand("select @.@.servername, db_name()", connection2);

SqlDataReader sdr = command.ExecuteReader();

sdr.Read();

string serverName = sdr.GetString(0);

string dbName = sdr.GetString(1);

return new SqlConnection("Server=" + serverName + ";Database=" + dbName + ";Trusted_Connection=yes;Enlist=false");

}

</code>

|||I receive the same error when attempting to connect to another db instance. What was the solution for this problem?

Help with Multiple connections in a CLR stored procedure

Here's what I'm trying to accomplish:

1. Open a connection and retrieve a datareader, using the context connection.
2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection.

The problem I have is that I can't open the second connection. Here's some sample code:

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure]
public static void up_TestClr() {

//Use the current context connection
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT TOP 100 AccountId FROM Account";

conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {

reader.Close();
conn.Close();

}

};

When I attempt to execute the procedure, I get the following:

Msg 6549, Level 16, State 1, Procedure up_TestClr, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'up_TestClr':

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.SqlClient.SqlConnection.PermissionDemand()

at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at StoredProcedures.up_TestClr(String& dbName)

. User transaction, if any, will be rolled back.
Any suggestions? I've tried unsuccessfully to create an Asymetric key to mark my assembly for External Access:

USE master

CREATE ASYMMETRIC KEY SN FROM EXECUTABLE FILE = 'C:\Shc\SqlServerProject1.dll'

CREATE LOGIN TestLogin FROM ASYMMETRIC KEY SN

GRANT EXTERNAL ACCESS ASSEMBLY TO TestLogin

Resulted in:

That should work fine. Thx.|||

Not sure how using a DataSet helps, but...

He is not opening a 2nd context connection. He is opening a 2nd connection that may happen to point to the same server/database as the context connection.

This may be needed for example to persist auditing information that needs to survive even if the context connection's transaction is later rolled back.

Are you saying this is not allowed?

My code attempts to create a 2nd connection like this, and I'm getting the same error when I attempt to .Open() it later:

<code>

</code>

|||I receive the same error when attempting to connect to another db instance. What was the solution for this problem?

Friday, February 24, 2012

Help with MDX selecting multiple attributes in Where clause

Given the following:
Dimension Name: Issues
Measure: Issue Count
Dimension Attributes: Priority (Values 1,2,3,4)
Severity (Values 1,2,3,4)
How can I format an MDX statement to select Issues Where (Priority = 1 or
Priority = 2) and (Severity = 1 or Severity = 2)?
When I try the following:
select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
From [Issues DB]
where
{
([Issues].[Priority].&[1]),([Issues].[Priority].&[2]),
([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
}
I get the error referenced below:
"Members belong to different hierarchies in the function."
Any pointers would be greatly appreciated!
~Steven
I guess you treat Priority and Severity as same level and put them between
{}, which is for a set.
Try this:
(
{[Issues].[Priority].&[1], [Issues].[Priority].&[2]},
{[Issues].[Severity].&[1], [Issues].[Severity].&[2]}
)
In your case, I would say put these two properties in 2 hierarchies. which
may improve the performance.
Guangming
"Steven" wrote:

> Given the following:
>
> Dimension Name: Issues
> Measure: Issue Count
> Dimension Attributes: Priority (Values 1,2,3,4)
> Severity (Values 1,2,3,4)
>
> How can I format an MDX statement to select Issues Where (Priority = 1 or
> Priority = 2) and (Severity = 1 or Severity = 2)?
>
> When I try the following:
>
> select [Measures].[Issue Count] on columns,
> ([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
> From [Issues DB]
> where
> {
> ([Issues].[Priority].&[1]),([Issues].[Priority].&[2]),
> ([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
> }
>
> I get the error referenced below:
>
> "Members belong to different hierarchies in the function."
>
> Any pointers would be greatly appreciated!
>
> ~Steven
>
>

Help with MDX selecting multiple attributes in Where clause

Given the following:
Dimension Name: Issues
Measure: Issue Count
Dimension Attributes: Priority (Values 1,2,3,4)
Severity (Values 1,2,3,4)
How can I format an MDX statement to select Issues Where (Priority = 1 or
Priority = 2) and (Severity = 1 or Severity = 2)?
When I try the following:
select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/20
05]) on rows
From [Issues DB]
where
{
([Issues].[Priority].&[1]),([Issues].[Priority].&[2]
),
([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
}
I get the error referenced below:
"Members belong to different hierarchies in the function."
Any pointers would be greatly appreciated!
~StevenI guess you treat Priority and Severity as same level and put them between
{}, which is for a set.
Try this:
(
{[Issues].[Priority].&[1], [Issues].[Priority].&
1;2]},
{[Issues].[Severity].&[1], [Issues].[Severity].&
1;2]}
)
In your case, I would say put these two properties in 2 hierarchies. which
may improve the performance.
Guangming
"Steven" wrote:

> Given the following:
>
> Dimension Name: Issues
> Measure: Issue Count
> Dimension Attributes: Priority (Values 1,2,3,4)
> Severity (Values 1,2,3,4)
>
> How can I format an MDX statement to select Issues Where (Priority = 1 or
> Priority = 2) and (Severity = 1 or Severity = 2)?
>
> When I try the following:
>
> select [Measures].[Issue Count] on columns,
> ([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/
2005]) on rows
> From [Issues DB]
> where
> {
> ([Issues].[Priority].&[1]),([Issues].[Priority].&[
2]),
> ([Issues].[Severity].&[1]), [Issues].[Severity].&[
2]
> }
>
> I get the error referenced below:
>
> "Members belong to different hierarchies in the function."
>
> Any pointers would be greatly appreciated!
>
> ~Steven
>
>

Help with max()

I have two tables:
TABLE1 has policy_id and subm_no, multiple subm_no's for every policy_id.
TABLE2 has policy_id
I'm doing a join, WHERE table1.policy_id = table2.policy_id.
I need to display the value of many fields from both tables where
table1.subm_no is the max value of that subm_no FOR THAT POLICY_ID. Can
someone point me in the right direction? Thanks.SELECT Table1.*, Table2.*
FROM Table1
JOIN Table2 ON Table1.policy_id = Table2.policy_id
WHERE Table1.Subm_No =
(
SELECT MAX(Subm_No)
FROM Table1 Tx
WHERE Tx.policy_id = Table1.policy_id
)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1d98a20592f0f5359898f9@.msnews.microsoft.com...
> I have two tables:
> TABLE1 has policy_id and subm_no, multiple subm_no's for every policy_id.
> TABLE2 has policy_id
> I'm doing a join, WHERE table1.policy_id = table2.policy_id.
> I need to display the value of many fields from both tables where
> table1.subm_no is the max value of that subm_no FOR THAT POLICY_ID. Can
> someone point me in the right direction? Thanks.

Help with Linked Servers

Hi Everyone,

I am developing a reporting database server that will use multiple data sources to construct its query (SQL Server 2005, Oracle 9i, and DB2). I successfully linked all 3 databases to my SQL Server 2005 and I am ready to create the report queries. I would like to know what is the best method to do this. Should I refer to the linked servers by four part naming or use a OPENQUERY query. When using a OPENQUERY, you cannot specify parameters to pass to the linked server which makes me question its performance. Can someone please suggest the best way to do this?

Thank you in advance

-Sam

The 'best' method is most likely the four part naming. It allows you to create more robust queries.

OPENQUERY is best used when the remote server is rarely used and for security (or other) reasons, having a 'linked server' is not appropriate. If the remote server is frequently used, then, by all means, add it as a linked server and use the four part names.

Sunday, February 19, 2012

Help with Join Query Please

Hi All,
I need help figuring out a query.
I have two tables:
REQ and PO
The schema here is that there can be multiple PO rows per a single REQ row.
example:
REQ PO
--
Requistion1
PO1
PO2
PO3
I am creating a query that counts the number of REQ ROWS per Person
and the number of NULL Columns in the Row, including the PO Columns.
My problem is that the [Total Requests] SUM includes the PO Records and I
don't wan that.
Below is my query. Any help would be greatly appreciated.
Thanks,
John.
/*--*/
SELECT Assignedto,
COUNT(*) AS [Total Requests],
PRStatus AS [Status],
SUM(CASE WHEN (Region Is Null) Then 1 Else 0 End) AS [Region],
SUM(CASE WHEN (OrderList Is Null) Then 1 Else 0 End) AS [OrderList],
SUM(CASE WHEN (ClientName Is Null) Then 1 Else 0 End) AS [Client Name],
SUM(CASE WHEN (PRStatus Is Null) Then 1 Else 0 End) AS [PRStatus ],
/*---*/
/*PO Data*/
SUM(CASE WHEN (P.Supplier Is Null) Then 1 Else 0 End) AS [Supplier],
SUM(CASE WHEN (P.PONumber Is Null) Then 1 Else 0 End) AS [PO Number],
SUM(CASE WHEN (P.DatePOCreated Is Null) Then 1 Else 0 End) AS [Date PO
Created],
SUM(CASE WHEN (P.DatePOAnticipatedShip Is Null) Then 1 Else 0 End) AS [Date
PO Anticipated Ship],
/*---*/
FROM INTReq R
LEFT OUTER JOIN INTReq_PO P ON R.RID = P.RID
WHERE (PRStatus <> 'Cancelled')
GROUP BY Assignedto, PRStatus
Order By Assignedto, PRStatusJohn wrote:
> Hi All,
> I need help figuring out a query.
> I have two tables:
> REQ and PO
> The schema here is that there can be multiple PO rows per a single
> REQ row. example:
> REQ PO
> --
> Requistion1
> PO1
> PO2
> PO3
I think I understand what you mean, but you should eliminate all ambiguity
by following the recommendations here: www.aspfaq.com/5006

> I am creating a query that counts the number of REQ ROWS per Person
> and the number of NULL Columns in the Row, including the PO Columns.
> My problem is that the [Total Requests] SUM includes the PO Records
> and I don't wan that.
> Below is my query. Any help would be greatly appreciated.
>
Now I'm really lost. Please provide DDL, sample data and desired results (in
tabular format)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||TRY
count(distinct REQ) as 'TotalRequest'
"John" <jrugo@.patmedia.net> wrote in message
news:uvCTGe2zFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I need help figuring out a query.
> I have two tables:
> REQ and PO
> The schema here is that there can be multiple PO rows per a single REQ
> row.
> example:
> REQ PO
> --
> Requistion1
> PO1
> PO2
> PO3
> I am creating a query that counts the number of REQ ROWS per Person
> and the number of NULL Columns in the Row, including the PO Columns.
> My problem is that the [Total Requests] SUM includes the PO Records and I
> don't wan that.
> Below is my query. Any help would be greatly appreciated.
> Thanks,
> John.
> /*--*/
> SELECT Assignedto,
> COUNT(*) AS [Total Requests],
> PRStatus AS [Status],
> SUM(CASE WHEN (Region Is Null) Then 1 Else 0 End) AS [Region],
> SUM(CASE WHEN (OrderList Is Null) Then 1 Else 0 End) AS [OrderList],
> SUM(CASE WHEN (ClientName Is Null) Then 1 Else 0 End) AS [Client Name],
> SUM(CASE WHEN (PRStatus Is Null) Then 1 Else 0 End) AS [PRStatus ],
> /*---*/
> /*PO Data*/
> SUM(CASE WHEN (P.Supplier Is Null) Then 1 Else 0 End) AS [Supplier],
> SUM(CASE WHEN (P.PONumber Is Null) Then 1 Else 0 End) AS [PO Number],
> SUM(CASE WHEN (P.DatePOCreated Is Null) Then 1 Else 0 End) AS [Date PO
> Created],
> SUM(CASE WHEN (P.DatePOAnticipatedShip Is Null) Then 1 Else 0 End) AS
> [Date PO Anticipated Ship],
> /*---*/
> FROM INTReq R
> LEFT OUTER JOIN INTReq_PO P ON R.RID = P.RID
> WHERE (PRStatus <> 'Cancelled')
> GROUP BY Assignedto, PRStatus
> Order By Assignedto, PRStatus
>
>|||Yup, that did the trick :)
Thank you very much for you help.
John.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:e3zsPK3zFHA.2912@.TK2MSFTNGP10.phx.gbl...
> TRY
> count(distinct REQ) as 'TotalRequest'
>
> "John" <jrugo@.patmedia.net> wrote in message
> news:uvCTGe2zFHA.3408@.TK2MSFTNGP09.phx.gbl...
>

Wednesday, March 28, 2012

Help with SQL 2005 Report Designer with multiple data sets

Help with sproc with one parameter that can contain multiple values

Friday, March 23, 2012

Help with search code

Monday, March 19, 2012

Help with Query - Insert multiple rows and link between tables.

Monday, March 12, 2012

Help with query

Monday, February 27, 2012

Help with multiple Left Joins

Help with multiple jobs failing

Help with multiple jobs failing

Help with multiple jobs failing

Help with Multiple inserts

Help with multiple IIFs, or need suggestion of better solution.

Help with multiple counts..

Help with Multiple connections in a CLR stored procedure

Help with Multiple connections in a CLR stored procedure

Friday, February 24, 2012

Help with MDX selecting multiple attributes in Where clause

Help with MDX selecting multiple attributes in Where clause

Help with max()

Help with Linked Servers

Sunday, February 19, 2012

Help with Join Query Please

Max Query

Blog Archive

About Me