_hypmv error

I just tried to update a column type from varchar(32) to varchar(64) in a
table,but got some dependency error like:
The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
But,in the dependency listing for the table I cant view any of the above,but
I have
got admin access.What are these dependecies for?any pointer will be greatly
appreciated.
A wild guess is that someone has been running Index Tuning izard which has created "Hypothetical
Materialized Views" (called Indexed Views), and these from some reason haven't been dropped. I'd
check if these are views and if your applications aren't using these, consider dropping them, using
DROP VIEW command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"c_ani" <cani@.discussions.microsoft.com> wrote in message
news:53B89EB6-C82A-49B2-96C2-84A98CB02FC0@.microsoft.com...
>I just tried to update a column type from varchar(32) to varchar(64) in a
> table,but got some dependency error like:
> The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
> But,in the dependency listing for the table I cant view any of the above,but
> I have
> got admin access.What are these dependecies for?any pointer will be greatly
> appreciated.
sql

_hypmv error

I just tried to update a column type from varchar(32) to varchar(64) in a
table,but got some dependency error like:
The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
But,in the dependency listing for the table I cant view any of the above,but
I have
got admin access.What are these dependecies for?any pointer will be greatly
appreciated.A wild guess is that someone has been running Index Tuning izard which has created "Hypothetical
Materialized Views" (called Indexed Views), and these from some reason haven't been dropped. I'd
check if these are views and if your applications aren't using these, consider dropping them, using
DROP VIEW command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"c_ani" <cani@.discussions.microsoft.com> wrote in message
news:53B89EB6-C82A-49B2-96C2-84A98CB02FC0@.microsoft.com...
>I just tried to update a column type from varchar(32) to varchar(64) in a
> table,but got some dependency error like:
> The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
> But,in the dependency listing for the table I cant view any of the above,but
> I have
> got admin access.What are these dependecies for?any pointer will be greatly
> appreciated.

[SSIS] : STDEV in Derived Columns

Hello,

Does anyone have already tried to calculate a standard deviation (STDEV) in a derived column ?

Any help is welcome ;-)

Cheers,

Bertrand

Bertrandr wrote:

Hello,

Does anyone have already tried to calculate a standard deviation (STDEV) in a derived column ?

Any help is welcome ;-)

Cheers,

Bertrand

Have you looked into the documentation?

http://msdn2.microsoft.com/en-us/library/ms141671.aspx

it looks to me thta function is not available...you may want to do that in the DB or create a script component.

|||

Haven't calculated standard deviation in a derived column, but rather in an Async script component (i.e. Synchronous Input Id = 0 on the first output). The data being passed in to this transform is DT_CY data type (currency) called TotalDue. The data passed out is a DT_R8 on the output called StdDev.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Private rowCount As Integer = 0

Private columnSum As Double = 0

Private columnSumOfSquares As Double = 0

Public Overrides Sub InputMain_ProcessInput(ByVal Buffer As InputMainBuffer)

While Buffer.NextRow()

InputMain_ProcessInputRow(Buffer)

End While

If Buffer.EndOfRowset Then

With OutputMainBuffer

.AddRow()

.StdDev = StdDev(rowCount, columnSum, columnSumOfSquares)

End With

OutputMainBuffer.SetEndOfRowset()

End If

End Sub

Public Overrides Sub InputMain_ProcessInputRow(ByVal Row As InputMainBuffer)

rowCount = rowCount + 1

columnSum = columnSum + Row.TotalDue

columnSumOfSquares = columnSumOfSquares + Math.Pow(Row.TotalDue, 2)

End Sub

Private Function StdDev(ByVal Rows As Integer, ByVal sum As Double, ByVal sumOfSquares As Double) As Double

Dim topSumVal As Double = (rowCount * columnSumOfSquares) - (Math.Pow(columnSum, 2))

Return Math.Sqrt(topSumVal / (rowCount * (rowCount - 1)))

End Function

End Class

|||Bertrand,
There has been a feature suggestion posted for SSIS with respect to statistics. Please visit the link and vote. Please add your comments as well.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253840

Thanks,
Phil|||

Phil Brammer wrote:

Bertrand,
There has been a feature suggestion posted for SSIS with respect to statistics. Please visit the link and vote. Please add your comments as well.

https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=253840

Thanks,
Phil

Phil,

Are you sure that's the right link? it takes me to 'Page Not Found'.

-Jamie

|||

Jamie Thomson wrote:

Phil Brammer wrote:

Bertrand,
There has been a feature suggestion posted for SSIS with respect to statistics. Please visit the link and vote. Please add your comments as well.

https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=253840

Thanks,
Phil

Phil,

Are you sure that's the right link? it takes me to 'Page Not Found'.

-Jamie

Good catch... When I visited the link, it redirected to a URL without "SQLServer" in the path... The link has been updated in my original post...

[SQLDatasource]Dynamic selection of column

Hey, I have a search form with a selectbox. This selectbox contains the columnnames.
I want when I put a text in a textbox and select a value in the selectbox and click submit that it search database.The Columnnames I put in a session.

If you see I have put in the querystring as columnname @.sescolumn which I have initialised as asp:sessionparameter.
But it gives no results. When I put @.sescolumn between [] like normal columnnames are it doesn't work also.

Can someon put my on the right path?

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring%>" SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where @.sescolumn like @.seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" > <SelectParameters> <asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" /> <asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" /> <asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" /> <asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" /> </SelectParameters> </asp:SqlDataSource>

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where ((@.sescolumn='AutoID' AND [AutoID] LIKE @.seskeyword) OR (@.sescolumn='Merk' AND [Merk] LIKE @.seskeyword) OR (@.sescolumn='Kleur' AND [Kleur] LIKE @.seskeyword) OR (@.sescolumn='Type' AND [Type] LIKE @.seskeyword) OR (@.sescolumn='Autotype' AND [Autotype] LIKE @.seskeyword) OR (@.sescolumn='prijs' AND [prijs] LIKE @.seskeyword) OR (@.sescolumn='Zitplaatsen' AND [Zitplaatsen] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding1' AND [Afbeelding1] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding2' AND [Afbeelding2] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding3' AND [Afbeelding3] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding4' AND [Afbeelding4] LIKE @.seskeyword)) and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource>

Or...

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where CASE @.sesscolumn WHEN 'AutoID' THEN [AutoID] WHEN 'Merk' THEN [Merk] WHEN 'Kleur' THEN [Kleur] WHEN 'Type' THEN [Type] WHEN 'Autotype' THEN [Autotype] WHEN 'prijs' THEN [prijs] WHEN 'Zitplaatsen' THEN [Zitplaatsen] WHEN 'Afbeelding1' THEN [Afbeelding1] WHEN 'Afbeelding2' THEN [Afbeelding2] WHEN 'Afbeelding3' THEN [Afbeelding3] WHEN 'Afbeelding4' THEN [Afbeelding4] END LIKE @.seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource>

|||

Damn didn't knew that this was possible. Thanks a lot mate.

Now I have just to convert some columns in where clause to type of string because my session is a string.
Going to find this out

Thanks again

[SQL Serevr 2005 Express] Loading data from an Excel sheet

Hi,

Is it possible to load data from an Excel sheet column, into a DB column within SQL Server Express Edition? I'm able to copy data from the DB talbe column into the Excel sheet, but not the other way, which forces me to enter data row by row, manually.

Thanks,

Alon

I describe one way I can do:

Create your table with the datastructure you want from VWD IDE for your batabase in the App_Data folder.

1.Select data from your excel columns and copy.

2.Open your table, highlight one blank row, right-click the highlighted area, from the manu select paste.

You already know how to copy data from SQL2005 Express to excel. It is sort of the same thing.

You may need to watch your column datatype.

|||

Hi limno,

Thanks for the detailed answer.

I tried it before, but unfortunately VWD doesn't support Paste of multiple cells. This means that if you'll copy several cells (within 1 column) from the Excel sheet, and try to paste it in the DB table (in VWD), as you described in step #2, you'll notice that only 1 cell, the one you highlighted, is affected.

This is, unless I'm missing something very basic...

Thanks,

Alon

|||

You paste over the highlighted row. That means all clomns in your table are highlighted.

If you highlight only one cell, it is true that everything goes in that cell.

Hope this helps.

How to insert a byte() to a blob column?

Hi everybody,

I don't where to asked this question in this forum. How do you insert to a column of a table with a blob or binary datatype if there is one, from a byte() datatype? What is wrong? How can I fixed this? I need help. Thanks.

Code:
string sqlText = "Insert table1(id, dataByte) values('" + id + "'," + byteData + ")";

OdbcCommand cmd = new OdbcCommand(sqlText, odConn);

//opening connection here

int iRes = cmd.ExecuteNonQuery();

Result:
iRes = -1
den2005parameterize, parameterize, parameterize
read up on parameters

on a side note - never build sql!
on another side note. . . don't use odbc (there are bugs in the MDAC sql odbc driver)
use oledb. . .
better yet - use the sqlclient library

untested code (might have missed a particular point but this is the gist):

SqlDbCommand cmd = new SqlDbCommand("Insert table1(id, dataByte) values(@.id , @.data)", sqlConn);
cmd.Parameters["@.id"].ParameterValue = id;
cmd.Parameters["@.data"].ParameterValue = byteData;
int iRes = cmd.ExecuteNonQuery()

Research "how to store an image in a database" - its the same concept.
Again. . . parameterize your queries.
Insist that your peers do the same.

Security - Performance - Maintainence

and this question belongs in .Net Data Access Forum|||Thanks for reply, Blair Allen. I solved this my problem now is retrieving this BLOB from database and converting it to byte() and loading it to a Micorosft.Ink object using Ink.Load() method. The error occurs at Ink.Load() statement. Can anyone help? Thanks for advise. I'll post this problem at .Net Data Access Forum.

den2005|||I think this is it:
just hacked, not checked

byte[] bytes = null;
/* first get the size. . . */
int num = MySqlDataReader.GetBytes("myBlobField", 0, null, 0, int.MaxValue);
if (num != 0)
{
/* allocate the bytes */
bytes = new byte[num];
/* load the bytes */
MySqlDataReader.GetBytes("myBlobField", 0, bytes, 0,num)
}

cheers|||Thanks Blair Allen for reply I used a different approach. I converted the byte() to a base64 string format and then store it as a Text data in database and retrieving it as string and used Convert.FromBase64String() method to convert it back to byte() and load it to Ink.Load() and it works.

den2005

Getting Unique entire row records based on one unique column..

Hi,

I like to get all records(all columns) for any employee who has more than 2 unique SuggestedTraining..As simple as possible and execution (performance optimized) speed is as fast as possible.

Data:

Table structure below

Assessment Table

AssessmentID EmployeeID CourseID SuggestedTraining Date ....more columns
1 4 1 'Training 1' <date>
2 2 2 'Training 2' <date>
3 1 4 'Training 1' <date>
4 2 1 'Training 2' <date>
5 4 2 'Training 1' <date>
6 3 3 '' <date>
7 2 5 'Training 3' <date>
8 5 7 'Training 2' <date>
9 7 4 'Training 2' <date>
10 5 3 'Training 1' <date>
11 5 6 'Training 3' <date>

Desired Result:

AssessmentID EmployeeID CourseID SuggestedTraining Date
4 2 1 'Training 2' <date>
7 2 5 'Training 3' <date>
8 5 7 'Training 2' <date>
10 5 3 'Training 1' <date>
11 5 6 'Training 3' <date>

Try the example below.

Chris

Code Snippet

DECLARE @.AssessmentTable TABLE

(

AssessmentID INT,

EmployeeID INT,

CourseID INT,

SuggestedTraining VARCHAR(20)

)

INSERT INTO @.AssessmentTable(AssessmentID, EmployeeID, CourseID, SuggestedTraining)

SELECT 1, 4, 1, 'Training 1' UNION ALL

SELECT 2, 2, 2, 'Training 2' UNION ALL

SELECT 3, 1, 4, 'Training 1' UNION ALL

SELECT 4, 2, 1, 'Training 2' UNION ALL

SELECT 5, 4, 2, 'Training 1' UNION ALL

SELECT 6, 3, 3, '' UNION ALL

SELECT 7, 2, 5, 'Training 3' UNION ALL

SELECT 8, 5, 7, 'Training 2' UNION ALL

SELECT 9, 7, 4, 'Training 2' UNION ALL

SELECT 10, 5, 3, 'Training 1' UNION ALL

SELECT 11, 5, 6, 'Training 3'

SELECT at.AssessmentID,

at.EmployeeID,

at.CourseID,

at.SuggestedTraining

FROM @.AssessmentTable at

INNER JOIN (SELECT atg.EmployeeID

FROM @.AssessmentTable atg

GROUP BY atg.EmployeeID

HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2) t

ON t.EmployeeID = at.EmployeeID

|||

Hi Chris,

I cannot do the insertion because simply I do not know the data at runtime,

Code Snippet

INSERT INTO @.AssessmentTable(AssessmentID, EmployeeID, CourseID, SuggestedTraining)

SELECT 1, 4, 1, 'Training 1' UNION ALL

SELECT 2, 2, 2, 'Training 2' UNION ALL

SELECT 3, 1, 4, 'Training 1' UNION ALL

SELECT 4, 2, 1, 'Training 2' UNION ALL

SELECT 5, 4, 2, 'Training 1' UNION ALL

SELECT 6, 3, 3, '' UNION ALL

SELECT 7, 2, 5, 'Training 3' UNION ALL

SELECT 8, 5, 7, 'Training 2' UNION ALL

SELECT 9, 7, 4, 'Training 2' UNION ALL

SELECT 10, 5, 3, 'Training 1' UNION ALL

SELECT 11, 5, 6, 'Training 3'

The desired result is to get records (all columns) of table based on Employees having more than 2 Suggested Training, empty Suggested Training and Duplicated Training are excluded...

|||

It is just sample data Chris offered to demonstrate his suggested solution.

Run all of the code as Chris posted to verify if the solution works for you.

According to your specifications, the Date is immaterial for the solution. If that is NOT correct, then refine your desired outcome so we can refine the suggested solutions.

|||

Hi,

My question how do I replace this part of script to be adaptable to actual data.

Code Snippet

SELECT 1, 4, 1, 'Training 1' UNION ALL

SELECT 2, 2, 2, 'Training 2' UNION ALL

SELECT 3, 1, 4, 'Training 1' UNION ALL

SELECT 4, 2, 1, 'Training 2' UNION ALL

SELECT 5, 4, 2, 'Training 1' UNION ALL

SELECT 6, 3, 3, '' UNION ALL

SELECT 7, 2, 5, 'Training 3' UNION ALL

SELECT 8, 5, 7, 'Training 2' UNION ALL

SELECT 9, 7, 4, 'Training 2' UNION ALL

SELECT 10, 5, 3, 'Training 1' UNION ALL

SELECT 11, 5, 6, 'Training 3'

Try to replace entire code it with this,

Code Snippet

DECLARE @.AssessmentTable TABLE

(

AssessmentID INT,

EmployeeID INT,

CatalogueCourseID INT,

SuggestedTraining nVARCHAR(500),

CompetencyModelID int

)

Insert Into @.AssessmentTable(AssessmentID, EmployeeID, CatalogueCourseID, SuggestedTraining, CompetencyModelID)

Select AssessmentID, EmployeeID, CatalogueCourseID, SuggestedTraining, CompetencyModelID From Assessment

Where IsActive =1 AND SuggestedTraining <> ''

SELECT at.AssessmentID,at.EmployeeID,at.CatalogueCourseID,at.SuggestedTraining FROM @.AssessmentTable at

INNER JOIN (SELECT atg.EmployeeID FROM @.AssessmentTable atg GROUP BY atg.EmployeeID HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2) t

ON t.EmployeeID = at.EmployeeID

Actual Result:

AssessmentID EmployeeID CatalogueCourseID SuggestedTraining
9 9 8 Training Course 2
30 9 9 Training Course 3
42 9 9 Training Course 3 << Duplicate
43 9 7 Training Course 1
47 9 7 Training Course 1 << Duplicate
54 9 5 Training
48 12 7 Training Course 1
40 12 7 Training Course 1 << Duplicate
46 12 9 Training Course 3
..... More data below....

Still Desired Result is not achieved...

|||

As Arnie pointed out in a response to your previous post, you only need to use the following:

Code Snippet

SELECT at.AssessmentID,

at.EmployeeID,

at.CourseID,

at.SuggestedTraining

FROM @.AssessmentTable at

INNER JOIN (SELECT atg.EmployeeID

FROM @.AssessmentTable atg

GROUP BY atg.EmployeeID

HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2) t

ON t.EmployeeID = at.EmployeeID

The CREATE TABLE / INSERT statements were included in my original post solely to show that the concept works. All you need to do is take the above sample and replace '@.AssessmentTable' with your actual table name.

Chris

|||

Hi Chris,

I tried this code slightly modified it to exclude empty SuggestedTraining, and still there are duplicate Suggested Training in the results. How can I remove duplicated SuggestedTraining in the result set. Thanks.

Code Snippet

SELECT at.AssessmentID, at.EmployeeID, at.CatalogueCourseID, at.SuggestedTraining FROM Assessment at

INNER JOIN (SELECT atg.EmployeeID FROM Assessment atg Where SuggestedTraining <> ''

GROUP BY atg.EmployeeID HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2) t

ON t.EmployeeID = at.EmployeeID

|||

For the duplicates, which AssessmentID would you want to return - the maximum or the minimum? [therein lies a clue to the solution... ]

Chris

|||

Well,

Maximum is the choice, right now date is not considered, how would the sql statement would be if both maximum and date (latest) is considered?

|||

Try the code below.

Chris

Code Snippet

SELECT MAX(at.AssessmentID),

at.EmployeeID,

at.CourseID,

at.SuggestedTraining

--, MAX(at.DateColumn)

FROM @.AssessmentTable at

INNER JOIN (

SELECT atg.EmployeeID

FROM @.AssessmentTable atg

GROUP BY atg.EmployeeID

HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2

) t ON t.EmployeeID = at.EmployeeID

GROUP BY at.EmployeeID,

at.CourseID,

at.SuggestedTraining

|||

I run the sql script you provided and here is the result:

AssessmentID EmployeeID CourseID SuggestedTraining

26 4 8 Training Course 1
8 4 8 Training Course 2
58 8 NULL <<-- Should Not be included
6 8 16 Training Course 1 <<-- Should Not be included
57 9 NULL <<-- Should Not be included
54 9 5 Training
47 9 7 Training Course 1
9 9 8 Training Course 2
42 9 9 Training Course 3
55 12 NULL <<-- Should Not be included
1 12 3 Training Course 2
48 12 7 Training Course 1
46 12 9 Training Course 3
22 13 NULL <<-- Should Not be included
13 13 NULL TEST TRAIN
41 13 7 Training Course 1
31 13 8 Training Course 2
45 13 9 Training Course 3
16 26 NULL <<-- Should Not be included
18 26 7 Training Course 1 <<-- Should Not be included
53 82 7 Training Course 1
38 82 16 Test Training Course
28 150 7 Training Course 1
27 150 9 Training Course 3
33 179 3 Training Course 2
37 179 16 Test Training Course
36 180 NULL <<-- Should Not be included
35 180 8 Training Course 2 <<-- Should Not be included

I filter the empty Suggested Training using this modified sql script:

Code Snippet

SELECT MAX(at.AssessmentID), at.EmployeeID, at.CatalogueCourseID, at.SuggestedTraining FROM Assessment at

INNER JOIN (SELECT atg.EmployeeID FROM Assessment atg GROUP BY atg.EmployeeID HAVING COUNT

(DISTINCT atg.SuggestedTraining) >= 2) t ON t.EmployeeID = at.EmployeeID Where at.SuggestedTraining <> ''

GROUP BY at.EmployeeID, at.CatalogueCourseID,at.SuggestedTraining

And still did not get desired result...

AssessmentID EmployeeID CourseID SuggestedTraining

26 4 8 Training Course 1
8 4 8 Training Course 2
6 8 16 Training Course 1 <<-- Should Not be included
54 9 5 Training
47 9 7 Training Course 1
9 9 8 Training Course 2
42 9 9 Training Course 3
1 12 3 Training Course 2
48 12 7 Training Course 1
46 12 9 Training Course 3
13 13 NULL TEST TRAIN
41 13 7 Training Course 1
31 13 8 Training Course 2
45 13 9 Training Course 3
18 26 7 Training Course 1 <<-- Should Not be included
53 82 7 Training Course 1
38 82 16 Test Training Course
28 150 7 Training Course 1
27 150 9 Training Course 3
33 179 3 Training Course 2
37 179 16 Test Training Course
35 180 8 Training Course 2 <<-- Should Not be included

Here is Whole Data for Assessment table:

AssessmentID EmployeeID CourseID SuggestedTraining

1 12 3 Training Course 2
3 59 7 Training Course 1
6 8 16 Training Course 1
7 10 3
8 4 8 Training Course 2
9 9 8 Training Course 2
10 132 7 Training Course 1
11 25 NULL Training Course 1
12 12 7 Training Course 1
13 13 NULL TEST TRAIN
14 54 7 Training Course 1
15 28 NULL
16 26 NULL
17 29 NULL
18 26 7 Training Course 1
19 6 NULL
20 84 7 Training Course 1
21 139 7 Training Course 1
22 13 NULL
23 82 7 Training Course 1
24 127 7 Training Course 1
25 64 7 Training Course 1
26 4 8 Training Course 1
27 150 9 Training Course 3
28 150 7 Training Course 1
29 12 7 Training Course 1
30 9 9 Training Course 3
31 13 8 Training Course 2
32 177 NULL
33 179 3 Training Course 2
34 180 8 Training Course 2
35 180 8 Training Course 2
36 180 NULL
37 179 16 Test Training Course
38 82 16 Test Training Course
39 9 7 Training Course 1
40 12 7 Training Course 1
41 13 7 Training Course 1
42 9 9 Training Course 3
43 9 7 Training Course 1
44 13 9 Training Course 3
45 13 9 Training Course 3
46 12 9 Training Course 3
47 9 7 Training Course 1
48 12 7 Training Course 1
49 87 7 Training Course 1
50 9 NULL
51 72 7 Training Course 1
52 83 9 Training Course 1
53 82 7 Training Course 1
54 9 5 Training
55 12 NULL
56 7 NULL
57 9 NULL
58 8 NULL

|||

I've re-arranged the filters in the query to filter out the rows that you don't need - see below. This now returns the results as specified in your previous post.

Chris

Code Snippet

DECLARE @.AssessmentTable TABLE

(

AssessmentID INT,

EmployeeID INT,

CourseID INT,

SuggestedTraining VARCHAR(20)

)

INSERT INTO @.AssessmentTable (AssessmentID, EmployeeID, CourseID, SuggestedTraining)

SELECT 1, 12, 3, 'Training Course 2' UNION ALL

SELECT 3, 59, 7, 'Training Course 1' UNION ALL

SELECT 6, 8, 16, 'Training Course 1' UNION ALL

SELECT 7, 10, 3, '' UNION ALL

SELECT 8, 4, 8, 'Training Course 2' UNION ALL

SELECT 9, 9, 8, 'Training Course 2' UNION ALL

SELECT 10, 132, 7, 'Training Course 1' UNION ALL

SELECT 11, 25, NULL, 'Training Course 1' UNION ALL

SELECT 12, 12, 7, 'Training Course 1' UNION ALL

SELECT 13, 13, NULL, 'TEST TRAIN' UNION ALL

SELECT 14, 54, 7, 'Training Course 1' UNION ALL

SELECT 15, 28, NULL, '' UNION ALL

SELECT 16, 26, NULL, '' UNION ALL

SELECT 17, 29, NULL, '' UNION ALL

SELECT 18, 26, 7, 'Training Course 1' UNION ALL

SELECT 19, 6, NULL, '' UNION ALL

SELECT 20, 84, 7, 'Training Course 1' UNION ALL

SELECT 21, 139, 7, 'Training Course 1' UNION ALL

SELECT 22, 13, NULL, '' UNION ALL

SELECT 23, 82, 7, 'Training Course 1' UNION ALL

SELECT 24, 127, 7, 'Training Course 1' UNION ALL

SELECT 25, 64, 7, 'Training Course 1' UNION ALL

SELECT 26, 4, 8, 'Training Course 1' UNION ALL

SELECT 27, 150, 9, 'Training Course 3' UNION ALL

SELECT 28, 150, 7, 'Training Course 1' UNION ALL

SELECT 29, 12, 7, 'Training Course 1' UNION ALL

SELECT 30, 9, 9, 'Training Course 3' UNION ALL

SELECT 31, 13, 8, 'Training Course 2' UNION ALL

SELECT 32, 177, NULL, '' UNION ALL

SELECT 33, 179, 3, 'Training Course 2' UNION ALL

SELECT 34, 180, 8, 'Training Course 2' UNION ALL

SELECT 35, 180, 8, 'Training Course 2' UNION ALL

SELECT 36, 180, NULL, '' UNION ALL

SELECT 37, 179, 16, 'Test Training Course' UNION ALL

SELECT 38, 82, 16, 'Test Training Course' UNION ALL

SELECT 39, 9, 7, 'Training Course 1' UNION ALL

SELECT 40, 12, 7, 'Training Course 1' UNION ALL

SELECT 41, 13, 7, 'Training Course 1' UNION ALL

SELECT 42, 9, 9, 'Training Course 3' UNION ALL

SELECT 43, 9, 7, 'Training Course 1' UNION ALL

SELECT 44, 13, 9, 'Training Course 3' UNION ALL

SELECT 45, 13, 9, 'Training Course 3' UNION ALL

SELECT 46, 12, 9, 'Training Course 3' UNION ALL

SELECT 47, 9, 7, 'Training Course 1' UNION ALL

SELECT 48, 12, 7, 'Training Course 1' UNION ALL

SELECT 49, 87, 7, 'Training Course 1' UNION ALL

SELECT 50, 9, NULL, '' UNION ALL

SELECT 51, 72, 7, 'Training Course 1' UNION ALL

SELECT 52, 83, 9, 'Training Course 1' UNION ALL

SELECT 53, 82, 7, 'Training Course 1' UNION ALL

SELECT 54, 9, 5, 'Training' UNION ALL

SELECT 55, 12, NULL, '' UNION ALL

SELECT 56, 7, NULL, '' UNION ALL

SELECT 57, 9, NULL, '' UNION ALL

SELECT 58, 8, NULL, ''

SELECT MAX(at.AssessmentID) AS AssessmentID,

at.EmployeeID,

at.CourseID,

at.SuggestedTraining

--, MAX(at.DateColumn)

FROM @.AssessmentTable at

INNER JOIN (

SELECT atg.EmployeeID

FROM @.AssessmentTable atg

WHERE atg.SuggestedTraining <> ''

GROUP BY atg.EmployeeID

HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2

) t ON t.EmployeeID = at.EmployeeID

WHERE at.SuggestedTraining <> ''

GROUP BY at.EmployeeID,

at.CourseID,

at.SuggestedTraining

ORDER BY 1

|||Thanks Chris, right now, based on data I have, this problem is resolved, when another problem arises...I post it here...I have marked your last reply as answer...

[Q] cpu is full.. 100%

os : win2000
sql : sql2000 enterprise
table name : job
I created fulltext catalog
table is job column name : title
full population is every sunday am 5:00
incremental population is every 2hours from 7am to next day 3am
whenever server is running the both population, cpu is full 100%(see the
task manager )
Question : can i reduce the cpu use?
the columns are updated frequently are readcnt of job table
if i remove the readcnt column, the cpu use will reduce?
thanks...
below is the result of sp_spaceused job
name rows reserved data index_size unused
Jobs 259845 468808 KB 214264 KB 28128 KB 226416 KB
column_name type
title varchar(100)
findPrsn tinyint
ageZn varchar
applyRght varchar
emplrId varchar
seq int
readCnt int
sido tinyint
workTyp char
applyDcmnt varchar
findYmd2 smalldatetime
gugun tinyint
finishYmd bit
ymd smalldatetime
particular char
position varchar
logo varchar
endymd smalldatetime
jobtype1 tinyint
jobtype2 tinyint
jobtype3 tinyint
paytype char
pay1 int
pay2 bit
suprise tinyint
ts timestamp
visible bit
createdYmd smalldatetime
parkchangsoon,
The limited duration high CPU usage you are seeing during your scheduled
Incremental Population is expected and normal as there is a lot of internal
work being processed that is not only CPU intensive, but memory and disk i/o
intensive, but only for specific durations. You can reduce the priority of
the MSSearch service via sp_fulltext_service 'resource_usage' <value>, where
<value> is by default set to 3, and set it to 1 (background). However, this
will have the affect of causing the Incremental Population to take much
longer time to complete.
Since you are using SQL Server 2000, I'd highly recommend that you look at
"Change Tracking" and "Updated Index in Background" (see the BOL for more
info on Change Tracking) as this will eliminate the need for your scheduled
Incremental Population and allow your FT Catalog to be updated in near
real-time as the FT-enabled column is updated. This will eliminate the high
cpu usage you are seeing during the scheduled Incremental Population.
Regards,
John
"parkchangsoon" <dev_sr@.albaroot.com> wrote in message
news:#PZ$lvLXEHA.2972@.TK2MSFTNGP12.phx.gbl...
> os : win2000
> sql : sql2000 enterprise
> table name : job
> I created fulltext catalog
> table is job column name : title
> full population is every sunday am 5:00
> incremental population is every 2hours from 7am to next day 3am
> whenever server is running the both population, cpu is full 100%(see the
> task manager )
> Question : can i reduce the cpu use?
> the columns are updated frequently are readcnt of job table
> if i remove the readcnt column, the cpu use will reduce?
> thanks...
>
> below is the result of sp_spaceused job
> name rows reserved data index_size unused
> ----
> Jobs 259845 468808 KB 214264 KB 28128 KB 226416 KB
> column_name type
> --
> title varchar(100)
> findPrsn tinyint
> ageZn varchar
> applyRght varchar
> emplrId varchar
> seq int
> readCnt int
> sido tinyint
> workTyp char
> applyDcmnt varchar
> findYmd2 smalldatetime
> gugun tinyint
> finishYmd bit
> ymd smalldatetime
> particular char
> position varchar
> logo varchar
> endymd smalldatetime
> jobtype1 tinyint
> jobtype2 tinyint
> jobtype3 tinyint
> paytype char
> pay1 int
> pay2 bit
> suprise tinyint
> ts timestamp
> visible bit
> createdYmd smalldatetime
>

[help] ALTER TABLE: error message 4929

Hi,

I've the following error message:

-----
ALTER TABLE [dbo].[AnagraficoAutomezzi]
ALTER COLUMN [CodiceXTrascodifica] varchar(10)
Go

Server: messaggio 4929, livello 16, stato 1, riga 1
Cannot alter table 'AnagraficoAutomezzi' because it is being published for
replication.
-----

This is only part of a script with much more alter table…

How can I lunch the script without this problem?

Thanks for your help!
Giacomo

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/Well, you cant alter schema involved in replication all that easily. For
this, you can use sp_addmergecolumn (not certain if the name is correct but
its close). If you post other code samples we can try to see what can be
done

MC

"Giacomo" <no_spam@.grazie.itwrote in message
news:op.tgst4zpwt6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

>
Hi,
>
I've the following error message:
>
-----
ALTER TABLE [dbo].[AnagraficoAutomezzi]
ALTER COLUMN [CodiceXTrascodifica] varchar(10)
Go
>
Server: messaggio 4929, livello 16, stato 1, riga 1
Cannot alter table 'AnagraficoAutomezzi' because it is being published for
replication.
-----
>
This is only part of a script with much more alter table.
>
How can I lunch the script without this problem?
>
Thanks for your help!
Giacomo
>
>
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

|||I've no more code sample to post, it's just a script with some ALTER TABLE
to change columns name or type.
We use this script to update client's DB. But one of them use replication
and we are always in trouble to update this DB.|||One of the downsides to replication is that is complicates subsequent schema
changes. As ML mentioned, you need to run the appropriate sp_repl* procs
instead of ALTER TABLE. The details vary depending on the type of
replication and the changes you are making.

If your client established replication outside the scope of your normal
support agreement, I suggest you have then remove replication so that your
normal script can be run and then reestablish replication afterwards. It's
unreasonable to expect your upgrade script to handle a replicated schema
unless you have detailed knowledge of the replication topology.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Giacomo" <no_spam@.grazie.itwrote in message
news:op.tgt5pk06t6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

I've no more code sample to post, it's just a script with some ALTER TABLE
to change columns name or type.
We use this script to update client's DB. But one of them use replication
and we are always in trouble to update this DB.

|||Just to add something to my previous response. You could try using EM to
implement some of the changes, however I really wouldnt recommend this.
Changing schema for database involved in replication isnt something you
should do lightly.
Thats why I asked if you could post additional code that doesnt work for you
so we could suggest something.

MC

PS. Dan, its MC (Marko Culo) ;).

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netwrote in message
news:i4sUg.1248$NE6.754@.newssvr11.news.prodigy.com ...

Quote:

Originally Posted by

One of the downsides to replication is that is complicates subsequent

schema

Quote:

Originally Posted by

changes. As ML mentioned, you need to run the appropriate sp_repl* procs
instead of ALTER TABLE. The details vary depending on the type of
replication and the changes you are making.
>
If your client established replication outside the scope of your normal
support agreement, I suggest you have then remove replication so that your
normal script can be run and then reestablish replication afterwards.

It's

Quote:

Originally Posted by

unreasonable to expect your upgrade script to handle a replicated schema
unless you have detailed knowledge of the replication topology.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"Giacomo" <no_spam@.grazie.itwrote in message
news:op.tgt5pk06t6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

I've no more code sample to post, it's just a script with some ALTER

TABLE

Quote:

Originally Posted by

Quote:

Originally Posted by

to change columns name or type.
We use this script to update client's DB. But one of them use

replication

Quote:

Originally Posted by

Quote:

Originally Posted by

and we are always in trouble to update this DB.

>
>

|||PS. Dan, its MC (Marko Culo) ;).

Oops. Sorry, Marko.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"MC" <marko_culoNOSPAM@.yahoo.comwrote in message
news:eg0r1t$bqq$1@.ss408.t-com.hr...

Quote:

Originally Posted by

Just to add something to my previous response. You could try using EM to
implement some of the changes, however I really wouldnt recommend this.
Changing schema for database involved in replication isnt something you
should do lightly.
Thats why I asked if you could post additional code that doesnt work for
you
so we could suggest something.
>
>
MC
>
PS. Dan, its MC (Marko Culo) ;).
>
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netwrote in message
news:i4sUg.1248$NE6.754@.newssvr11.news.prodigy.com ...

Quote:

Originally Posted by

>One of the downsides to replication is that is complicates subsequent

schema

Quote:

Originally Posted by

>changes. As ML mentioned, you need to run the appropriate sp_repl* procs
>instead of ALTER TABLE. The details vary depending on the type of
>replication and the changes you are making.
>>
>If your client established replication outside the scope of your normal
>support agreement, I suggest you have then remove replication so that
>your
>normal script can be run and then reestablish replication afterwards.

It's

Quote:

Originally Posted by

>unreasonable to expect your upgrade script to handle a replicated schema
>unless you have detailed knowledge of the replication topology.
>>
>--
>Hope this helps.
>>
>Dan Guzman
>SQL Server MVP
>>
>"Giacomo" <no_spam@.grazie.itwrote in message
>news:op.tgt5pk06t6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

I've no more code sample to post, it's just a script with some ALTER

TABLE

Quote:

Originally Posted by

Quote:

Originally Posted by

to change columns name or type.
We use this script to update client's DB. But one of them use

replication

Quote:

Originally Posted by

Quote:

Originally Posted by

and we are always in trouble to update this DB.

>>
>>

>
>

[DTS.Pipeline] Warning: The output column "Column 5" (1038) on output "Flat File

[DTS.Pipeline] Warning: The output column "Column 5" (1038) on output "Flat File Source Output" (2) and component "Flat File Source" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

what the heck does this mean?

More or less what it says - you have a column that is not used by downstream component.

So the data flow issues a warning that it might be better to just remove this column to save buffer space. Of course it is just optimization warning and can be ignored, but if you want maximum performance do pay attention to such warnings.

[ask] how to run a trigger or store precedure in certain date?

hello, i have a database that will be updated on a certain date

i have a column "UPDATE_DATE" which specifies the updating date, my question is

"how should i make the trigger or stored procedure runs only on the date that has been specified"

thanks for the assistance

Create a job that runs once everyday and compares the date on the dat with the value in the column, and calls the proc if they match.|||

ok, i think that will be good idea since sqlexpress doesn't come with sqlagent

ok then, i'll try to code by that algorithm. thx anyway bro

[*-)]how to delete all columns except one column for the corresponding column name......?

hi friends,

i've a table with (columns) username, content,data,......... i need to delete all column names(i.e.,content,data,......) except username for the specified username. eg: consider username=mahendran. i've to delete the values in the content,data,............for the username=mahendran. but username should exist.

how to do that?pls help me.....

Use the following

DataTable.Columns.Remove takes the column name or column reference as argument.

To remove column using it's index, you can use DataTable.Columns.RemoveAt method.

|||

hi farooq,

i need the SQL query for delete command. i wrote

delete [contact],[title] from <table_name> where userid=<user_id>

but it is not working.what is the correct format?

|||

If you want to change your table structure, you useAlter Table along withDrop Column instead of Delete which is for your records. Here is a sample:

ALTERTABLE yourTable

DROPCOLUMN col2, col3

You can find more information about SQL DML(SQL Data Manipulation Language) and DDL(SQL Data Definition Language) if you are interested about the topic.

@local variable (newbie)

I am trying to run a query.

Declare ColNames_Csr Cursor
-- Open Cursor that contains all column names
for
Select Column_Name from Information_Schema.columns where Table_Name = @.TableName

Open ColNames_csr

Fetch Next From ColNames_csr into @.FieldName

While ...

Begin
.
.
.
Select @.FieldValue = (Select @.FieldName from Contacts)
Print @.FieldValue
.
.
.
end

@.SomeValue always returns the FieldName (EMail) not @.FieldName (name@.somewhere.com).

I am trying to roll through all the columns and see what the value is for @.fieldname

What am i missing?

Thanks
LJOriginally posted by LittleJonny
I am trying to run a query.

Declare ColNames_Csr Cursor
-- Open Cursor that contains all column names
for
Select Column_Name from Information_Schema.columns where Table_Name = @.TableName

Open ColNames_csr

Fetch Next From ColNames_csr into @.FieldName

While ...

Begin
.
.
.
Select @.FieldValue = (Select @.FieldName from Contacts)
Print @.FieldValue
.
.
.
end

@.SomeValue always returns the FieldName (EMail) not @.FieldName (name@.somewhere.com).

I am trying to roll through all the columns and see what the value is for @.fieldname

What am i missing?

Thanks
LJ

Query below returns value of @.FieldName - no field in table. Think about dynamic query ...

(Select @.FieldName from Contacts)|||Dynamic Query? Im not sure I understand.

@.FieldName = 'Name'

Select @.FieldValue = (Select @.FieldName from Contacts where contactid = 32)

Print @.FieldValue

Always Prinst "Name"

How do I get the data in Contacts.@.FieldName?

Thanks
LJ|||Originally posted by LittleJonny
Dynamic Query? Im not sure I understand.

@.FieldName = 'Name'

Select @.FieldValue = (Select @.FieldName from Contacts where contactid = 32)

Print @.FieldValue

Always Prinst "Name"

How do I get the data in Contacts.@.FieldName?

Thanks
LJ

Dynamic query is something like this:

create table test(id int, code varchar(10))
go
insert test values(1,'A')
insert test values(2,'B')
insert test values(3,'C')
go
create proc retvalue(@.sql varchar(8000),@.result varchar(50) output)
as
declare @.res varchar(50)
create table #tmp(res varchar(50))
insert #tmp exec(@.sql)
select @.result=res from #tmp
return
go
declare @.res varchar(50),@.sql varchar(8000),@.field varchar(50)
set @.field='code'
set @.sql='select '+@.field+' from test'
exec retvalue @.sql,@.res output
select @.res