Profile Error 17055

I applied a service pack to a third party application and
as of now I keep receiving the following in the sql
server log and application log every 1 minute or so.
How can I use the profiler on this to find out what's
happening?
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 17055
Date: 12/28/2003
Time: 11:54:26 AM
User: N/A
Computer: SERVERP01
Description:
18456 :
Login failed for user 'TRACK'.
Data:
0000: 18 48 00 00 0e 00 00 00 .H.....
0008: 0f 00 00 00 4a 00 52 00 ...J.R.
0010: 41 00 59 00 44 00 55 00 A.Y.D.U.
0018: 42 00 49 00 53 00 51 00 B.I.S.Q.
0020: 4c 00 50 00 30 00 31 00 L.P.0.1.
0028: 00 00 07 00 00 00 6d 00 .....m.
0030: 61 00 73 00 74 00 65 00 a.s.t.e.
0038: 72 00 00 00 r...
Thanks,
ShonetYou can use profiler to log connection attempts...Someone is trying to log
in using the login TRACK, which is a failed attempt... Watching this in
profiler will get you the name of the workstation, and name of the program
they are using... then you can track them down..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shonet" <anonymous@.discussions.microsoft.com> wrote in message
news:02bc01c3cd17$897e17f0$a601280a@.phx.gbl...
> I applied a service pack to a third party application and
> as of now I keep receiving the following in the sql
> server log and application log every 1 minute or so.
> How can I use the profiler on this to find out what's
> happening?
> Event Type: Information
> Event Source: MSSQLSERVER
> Event Category: (4)
> Event ID: 17055
> Date: 12/28/2003
> Time: 11:54:26 AM
> User: N/A
> Computer: SERVERP01
> Description:
> 18456 :
> Login failed for user 'TRACK'.
> Data:
> 0000: 18 48 00 00 0e 00 00 00 .H.....
> 0008: 0f 00 00 00 4a 00 52 00 ...J.R.
> 0010: 41 00 59 00 44 00 55 00 A.Y.D.U.
> 0018: 42 00 49 00 53 00 51 00 B.I.S.Q.
> 0020: 4c 00 50 00 30 00 31 00 L.P.0.1.
> 0028: 00 00 07 00 00 00 6d 00 .....m.
> 0030: 61 00 73 00 74 00 65 00 a.s.t.e.
> 0038: 72 00 00 00 r...
>
> Thanks,
> Shonet|||I managed to work this one out, but I'm please to see
what I came up with is was the same as your solution.
Excellent.
Thanks!!
Shonet
>--Original Message--
>You can use profiler to log connection
attempts...Someone is trying to log
>in using the login TRACK, which is a failed attempt...
Watching this in
>profiler will get you the name of the workstation, and
name of the program
>they are using... then you can track them down..
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Shonet" <anonymous@.discussions.microsoft.com> wrote in
message
>news:02bc01c3cd17$897e17f0$a601280a@.phx.gbl...
>> I applied a service pack to a third party application
and
>> as of now I keep receiving the following in the sql
>> server log and application log every 1 minute or so.
>> How can I use the profiler on this to find out what's
>> happening?
>> Event Type: Information
>> Event Source: MSSQLSERVER
>> Event Category: (4)
>> Event ID: 17055
>> Date: 12/28/2003
>> Time: 11:54:26 AM
>> User: N/A
>> Computer: SERVERP01
>> Description:
>> 18456 :
>> Login failed for user 'TRACK'.
>> Data:
>> 0000: 18 48 00 00 0e 00 00 00 .H.....
>> 0008: 0f 00 00 00 4a 00 52 00 ...J.R.
>> 0010: 41 00 59 00 44 00 55 00 A.Y.D.U.
>> 0018: 42 00 49 00 53 00 51 00 B.I.S.Q.
>> 0020: 4c 00 50 00 30 00 31 00 L.P.0.1.
>> 0028: 00 00 07 00 00 00 6d 00 .....m.
>> 0030: 61 00 73 00 74 00 65 00 a.s.t.e.
>> 0038: 72 00 00 00 r...
>>
>> Thanks,
>> Shonet
>
>.
>

Product Sub-Typing

Hi,

I am interested in opinions on the following,

main(product_id, short_text, price, type)
productTypes(type_id, type)
productTables(type_id, tableName)
productA(product_id, field1, field2)
productB(product_id, field1, field2)

To retrieve all products of type 'A', one must know the table name, in this case 'productA'.

Here is one method.

Create a table that contains the table name that corresponds to each product type, thus the stored procedure only needs to recieve the type_id which can be used to obtain the name of the respective table....and the result of all this would be dynamic SQL, right? I would not recommend it.sql

Product qty in a given branch

Hi,
I'm new to queries and I'm stuck with joins...
I have the following tables, and would like to get the total of the
products, given a BranchId:
table Product
ProductId: PK
ProductName
table Branch
BranchId
table Inventory
InventoryId: PK
BranchId: FK to Branch.BranchId
table InventoryDetail
InventoryDetailId: PK
InventoryId: FK to Inventory.InventoryId
ProductId: FK to Product.ProductId
Quantity
Here is what I started:
CREATE PROCEDURE SelectProductByName(@.BranchId INT, @.ProductName
NVARCHAR(100))
AS
SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
FROM Product AS P
LEFT JOIN InventoryDetail AS ID ON ID.ProductId = P.ProductId
/*And waht now with*/
RETURNHello, Michael
I guess you want something like this:
SELECT P.ProductId, P.ProductName, X.Qty
FROM Product AS P LEFT JOIN (
SELECT ID.ProductId, Sum(ID.Quantity) AS Qty
FROM InventoryDetail ID
INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
WHERE I.BranchId = @.BranchId
GROUP BY ID.ProductId
) X ON P.Product = X.ProductId
WHERE P.ProductName = @.ProductName
This query may also work, but the first query is better:
SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
FROM Product AS P
LEFT JOIN InventoryDetail ID ON P.ProductId = ID.ProductId
LEFT JOIN Inventory I ON ID.InventoryId = I.InventoryId
AND I.BranchId = @.BranchId
WHERE P.ProductName = @.ProductName
GROUP BY P.ProductId, P.ProductName
Razvan|||Hi Razvan,
Thank you very much for the help.
Why is the first first query better than the second one?
Also, I've come up with the following query. Is it correct?
SELECT P.ProductId, P.ProductName,
(SELECT SUM(ID.Quantity) FROM InventoryDetail AS ID
JOIN Inventory AS I ON I.InventoryId = ID.InventoryId
WHERE (I.BranchId = @.BranchId) AND (ID.ProductId = P.ProductId))
AS Qty
FROM Product AS P
WHERE (P.ProductName = @.ProductName)
Regards
Razvan Socol wrote:
> Hello, Michael
> I guess you want something like this:
> SELECT P.ProductId, P.ProductName, X.Qty
> FROM Product AS P LEFT JOIN (
> SELECT ID.ProductId, Sum(ID.Quantity) AS Qty
> FROM InventoryDetail ID
> INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
> WHERE I.BranchId = @.BranchId
> GROUP BY ID.ProductId
> ) X ON P.Product = X.ProductId
> WHERE P.ProductName = @.ProductName
> This query may also work, but the first query is better:
> SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
> FROM Product AS P
> LEFT JOIN InventoryDetail ID ON P.ProductId = ID.ProductId
> LEFT JOIN Inventory I ON ID.InventoryId = I.InventoryId
> AND I.BranchId = @.BranchId
> WHERE P.ProductName = @.ProductName
> GROUP BY P.ProductId, P.ProductName
> Razvan
>|||Hello, Michael

> Why is the first first query better than the second one?
Before testing the queries (because I had no DDL and sample data, see:
http://www.aspfaq.com/etiquette.asp?id=5006), I wrote that the first
query is better, because the second query seemed unsafe to me. Now that
I've tested it (by translating it for one of my own schemas), I can say
that the second query is plain wrong: it doesn't return the expected
results. I intended to write the second query this way:
SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
FROM Product AS P
LEFT JOIN InventoryDetail ID
INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
ON P.ProductId = ID.ProductId AND I.BranchId = @.BranchId
WHERE P.ProductName = @.ProductName
GROUP BY P.ProductId, P.ProductName
Regarding the weird placement of the ON clause, see:
http://groups.google.com/group/micr...br />
2579509b

> Also, I've come up with the following query. Is it correct?
Yes, it is correct. Actually, I think it's identical (in terms of
performance) with my first query, but your query is better because is
more readable. The corrected second query (although it produces an
execution plan with the same cost as the other two), seems to me a bit
slower, because the "Stream Aggregate" step (the GROUP BY clause) is
done on a wider row. Also, it's a lot harder to understand, so that's
why I still think it's the worse among the three queries.
Razvan|||Hi Razvan,
Sorry, I didn't provide the DDL. Now that I know how to do it, I'll
provide it next time.
I'll go on with my query as I already understood it, keeping a copy of
yours if needed.
Thanks a lot
Razvan Socol wrote:
> Hello, Michael
>
>
> Before testing the queries (because I had no DDL and sample data, see:
> http://www.aspfaq.com/etiquette.asp?id=5006), I wrote that the first
> query is better, because the second query seemed unsafe to me. Now that
> I've tested it (by translating it for one of my own schemas), I can say
> that the second query is plain wrong: it doesn't return the expected
> results. I intended to write the second query this way:
> SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
> FROM Product AS P
> LEFT JOIN InventoryDetail ID
> INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
> ON P.ProductId = ID.ProductId AND I.BranchId = @.BranchId
> WHERE P.ProductName = @.ProductName
> GROUP BY P.ProductId, P.ProductName
> Regarding the weird placement of the ON clause, see:
> http://groups.google.com/group/micr... />
492579509b
>
>
> Yes, it is correct. Actually, I think it's identical (in terms of
> performance) with my first query, but your query is better because is
> more readable. The corrected second query (although it produces an
> execution plan with the same cost as the other two), seems to me a bit
> slower, because the "Stream Aggregate" step (the GROUP BY clause) is
> done on a wider row. Also, it's a lot harder to understand, so that's
> why I still think it's the worse among the three queries.
> Razvan
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Since you also did not give any specs, can I assume that inventory is
spread over branches? Can I assume the product is identified by some
industry standard code, such as a UPC? And why did you create that
useless inventory details table? An inventory is not like an invoice.
CREATE TABLE Products
(upc CHAR(13) NOT NULL PRIMARY KEY, --upc?
product_name CHAR(20) NOT NULL,
.);
CREATE TABLE Branches
(branch_id INTEGER NOT NULL PRIMARY KEY,
.);
CREATE TABLE Inventory
(branch_id INTEGER NOT NULL
REFERENCES Branches(branch_id),
upc CHAR(13) NOT NULL
REFERENCES Products(upc),
PRIMARY KEY (branch_id, upc),
qty_on_hand INTEGER DEFAULT 0 NOT NULL
CHECK (qty_on_hand >= 0)
The right design makes it easy:
SELECT P.upc, SUM(I.qty_on_hand)
FROM Products AS P
LEFT OUTER JOIN
Inventory AS I
ON P.upc= I.upc
GROUP BY P.upc;|||Hi Celko,
I already got an answer from Razvan.
I needed the inventory detail table to hold the different quantities
with their respective notes, such as bin location, qty of damaged items
included in the inventory,...
Now that I know how to post DDL, I'll do it next time. As for the sample
data, I don't have any yet. I'm starting to design the database from
scratch.
Thanks for the help
--CELKO-- wrote:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Since you also did not give any specs, can I assume that inventory is
> spread over branches? Can I assume the product is identified by some
> industry standard code, such as a UPC? And why did you create that
> useless inventory details table? An inventory is not like an invoice.
> CREATE TABLE Products
> (upc CHAR(13) NOT NULL PRIMARY KEY, --upc?
> product_name CHAR(20) NOT NULL,
> ..);
> CREATE TABLE Branches
> (branch_id INTEGER NOT NULL PRIMARY KEY,
> ..);
> CREATE TABLE Inventory
> (branch_id INTEGER NOT NULL
> REFERENCES Branches(branch_id),
> upc CHAR(13) NOT NULL
> REFERENCES Products(upc),
> PRIMARY KEY (branch_id, upc),
> qty_on_hand INTEGER DEFAULT 0 NOT NULL
> CHECK (qty_on_hand >= 0)
>
>
> The right design makes it easy:
> SELECT P.upc, SUM(I.qty_on_hand)
> FROM Products AS P
> LEFT OUTER JOIN
> Inventory AS I
> ON P.upc= I.upc
> GROUP BY P.upc;
>|||>> I needed the inventory detail table to hold the different quantities with
their respective notes, such as bin location, qty of damaged items included
in the inventory,...<<
That is the usual inventory design; several different quantity
attributes (on-hand, on order, on hold, damaged, expired, etc. Since
these are all distinct attributes of an inventory held at a p[articular
branch, you simply extend the table. That data and the needed check
constraints were not shown in your specs (or lack of specs, really).
There is no need to create a separate inventory_id., which is a poor
attempt at a surrogate for a branch_id. You are splitting the actual
primary key (branch_id, upc) across tables. Ask yourself what meaning
an inventory has apart from the goods it holds. Look at your
pseudo-code; what maps yhour magical "InventoryDetailId" to one and
only one product, and assures that it is the RIGHT product? Nothing.
The split key invites disaster.
Please consider a correct design instead.|||Designing a database is not so simple...
I've already changed my design several times. At first because of a lack
of knowledge in database design (this is actually my first database with
SqlServer, before this was 2 simple databases with MS Access)
If you don't mind I'll really need a help. I'll try to make it as simple
as I can.
Our company has 2 branches (I'll generalise this to several branches).
Products can be stocked in either of these branches.
When counting a product, we need the date of the counting, and the
details (quantity + comments, such as damaged, carton no, bin
location,... which are "free-hand" text note). Note that branches count
their products independently of each other.
So, I came up with these tables:
CREATE TABLE Product (
ProductId BIGINT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(50) NOT NULL,
..
)
CREATE TABLE Branch (
BranchId INT IDENTITY PRIMARY KEY,
BranchName NVARCHAR(50) NOT NULL,
..
)
CREATE TABLE Inventory (
InventoryId BIGINT IDENTITY PRIMARY KEY,
InventoryDate DATETIME NOT NULL,
BranchId INT NOT NULL REFERENCES Branch(BranchId),
..
)
CREATE TABLE InventoryDetail (
InventoryDetailId BIGINT IDENTITY PRIMARY KEY,
InventoryId BIG INT NOT NULL REFERENCES Inventory(InventoryId),
ProductId BIG INT NOT NULL REFERENCES Product(ProductId),
Quantity INT NOT NULL,
Comments NVARCHAR(100) NULL
)
As for sample data, I don't have any yet, but here is what we want:
Lady coat no 634:
Main branch: counted on 14/02/06
- 60 pcs, ctn 123
- 6 pcs, on the shelf (in the shop)
Second branch: counted on 16/02/06
- 3 pcs, on the shelf
Lady pant no 132:
Main branch: counted on 21/02/06
- 2 pcs, a customer has specially ordered these for himself
Lady shoes no 852:
Main branch: counted on 22/02/06
- 12 pcs, in stock shelf no 3
- 12 pcs, waiting to be sent to branch 2
- 2 pcs, in the shop
Second branch: counted on 20/02/06
- 0 pcs, placed the order from Main branch already
--CELKO-- wrote:
>
> That is the usual inventory design; several different quantity
> attributes (on-hand, on order, on hold, damaged, expired, etc. Since
> these are all distinct attributes of an inventory held at a p[articular
> branch, you simply extend the table. That data and the needed check
> constraints were not shown in your specs (or lack of specs, really).
> There is no need to create a separate inventory_id., which is a poor
> attempt at a surrogate for a branch_id. You are splitting the actual
> primary key (branch_id, upc) across tables. Ask yourself what meaning
> an inventory has apart from the goods it holds. Look at your
> pseudo-code; what maps yhour magical "InventoryDetailId" to one and
> only one product, and assures that it is the RIGHT product? Nothing.
> The split key invites disaster.
> Please consider a correct design instead.
>

Product level is insufficient error

Configuration: Windows XP Pro with SQL 2005 Workgroup Sp2

Hi, we have a package that runs fine in BI Studio but fails with the following error when executing it using DTExec.

Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Derived Column" (10660).

The package imports a CSV into a table mapping some columns. The MSDN article at http://msdn2.microsoft.com/en-us/library/ms143761.aspx describes features supported by different versions.

The article says OLE DB source/destination adapters are supported in WG edition but a couple of lines later seems to imply that all source/destination adapters are not supported in WG.

Could you clarify whether stuff like import of a csv into a table with some column mappings should work in WG edition? Also if we have a legacy SQL 2K package doing the same thing, will that work in WG edition? Thx.

Features/Integration Services Enhancements EE (32-bit) DE (32-bit) EE (64-bit) DE (64-bit) SE (32-bit) WG (32-bit) SSE (32-bit) SSEA (32-bit) SE (64-bit)

SQL Server Import and Export Wizard and supporting connections, source and destination adapters, and tasks

Yes

Yes

Yes

No

Yes

Execute SQL Task

Yes

Yes

Yes

No

Yes

OLE DB Source and Destination Adapters

Yes

Yes

Yes

No

Yes

SSIS command prompt tools

Yes

Yes

Yes

No

Yes

SSIS Package Designer

Yes

Yes

Yes

No

Yes

Legacy support for DTS packages

Yes

Yes

Yes

Yes

Yes

SSIS Service

Yes

Yes

No

No

Yes

All other source and destination adapters, tasks, and transformations, except for those listed below

Yes

Yes

No

No

Yes

You might want to be sure that you have actually installed the full SSIS client.|||

As Phil wrote, most likely you've only Workstation components installed and need to install full SSIS.

See Books Online and my blog for details:

http://msdn2.microsoft.com/en-US/library/aa337371.aspx

http://blogs.msdn.com/michen/archive/2006/11/11/ssis-product-level-is-insufficient.aspx

Product Level is Insufficient

I am receiving the following error when I try to execute an SSIS package that is saved to a structured file. I am using DTExec to execute the package.

Description: The task "Initialize Variables" cannot run on this edition of Integration Services. It requires a higher level edition.

After some research, I was able to find a supporting article in SQL Server 2005 BOL at http://msdn2.microsoft.com/en-us/library/aa337371.aspx

The issue was resolved by installing the Enterprise Edition of SQL Server Integration Services (Windows Service) on the local server (server where the package is saved and executed from). However, this goes against my original anticipation that the SSIS Windows Service would only need to be installed on a machine where I needed to store packages and/or monitor package execution. Not if I wanted to execute packages.

Unfortunately, my package is using advanced options (as identified in the BOL article previously referenced).

Am I missing something?

Sincerely,

Sean Fitzgerald

You are trying to execute these packages from a remote machine (such as your desktop and not the SQL Server), correct? You need to have a matching edition of the SSIS client tools loaded on your desktop to be able to run those packages remotely.|||You do need the SSIS installed where the packages runs.
It does not matter where the package is stored.

See my blog entries about this:
http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx
http://blogs.msdn.com/michen/archive/2006/11/11/ssis-product-level-is-insufficient.aspx

Processing Time

Hi,

I am verifying my reports processing time. I get the information from the Reporting Service DB - [ExecutionLogs] table. I have the following information:

[TimeEnd] – time that reports generation ends.

[TimeStart] - time that reports generation starts.

[TimeDataRetrieval] - amount of time spent running the data sources.

[TimeProcessing] - time spent processing the report.

[TimeRendering] - time spent generating the output format.

If this information is correct the following statement should be true:

([TimeEnd] - [TimeStart]) = ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering])

But it isn't, ([TimeEnd] - [TimeStart]) is always bigger then ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering]).

Why does this happen?

Regards,

Rodrigo

That's interesting. I've never looked to check that. Are you seeing a big difference? how much is it a % of total execution time?

I suspect that TimeDataRetrieval, TimeProcessing, TimeRendering are each recorded by the subsystem resposible for each of those. I would expect there to be a small amount of overhead for the engine to do it's stuff and transition between the various phases between start and end.

|||

I have the following results for different reports:

End-Start

(ms)

Sum

(ms)

%

5030 3717 73,8966203 17437 12009 68,870792 5047 3374 66,851595 4733 3142 66,3849567 23170 15348 66,2408287 4780 3134 65,5648536 15750 10129 64,3111111 13017 7849 60,2980718 11153 6548 58,7106608 13170 7660 58,1624905 8830 5119 57,9728199

The first column is ([TimeEnd] - [TimeStart])

The second column is ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering])

How can i find the cause of this difference ?

Regards,

Rodrigo

|||

The difference is consistently large, too large. From your observations i.e. looking at the screen with a stopwatch, which is the "more correct" value?

|||

Do the reports have multiple datasets?

If multiple datasets are present and transactions are not used, SSRS will execute dataset queries in parallel. However, since there is only one data retrieval value in the execution log, it will pick the time of the longest data retrieval thread.

In addition, time data retrieval + processing + rendering does not cover all the time between the start and the end time of the request. Generally however, the other operations performed should just be small fraction compared to ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering]).

-- Robert

Processing cube from SSAS - OLE DB error

I have made a new dimension and setup the reference in dimension usage. Every time I process the cube i got the following message

Error 1 OLE DB error: OLE DB or ODBC error: Invalid column name 'PReference'.; 42S22. 0 0

The value in this column is 0,1 or 2. I have set the value in all rows to 1 and still get the same error.

I have tried to create a new column - updated value to 1 and refreshed the datasource and changed the dimension and dimension usage and I still got the same error processing the cube.

I have tried to use another integer field in dimension usage and this works fine.

Maybe it is something on the table - i have tried almost all but I can't solve this problem.

Leo Pedersen

The error message "Invalid column name 'PReference'" is saying that an invalid column name was specified in the SQL query that AS generated. Run Profiler on the AS server and capture the SQL query. It should point you to the problem.

Processing Cube error message?

Sorry about this "too stupid to live" type question, but I'm processing my first cube and I get the following error message: MdxScript(Manufacturing DM) (18,5) Parser: The syntax for "," is incorrect.

Dimensions are processed, it is working on a fact table. Where do I go to find a file that I might look for a bad comma?

(SQL Svr 2005 32bit w/sp 2, Vista Ultimate 32bit, Visual Studio w/ sp1)

Sorry, posted too soon....found my problem.....error in calculation code.....D'oh!!!

Processing a cube dimension

I am trying to process a time dimension on a cube and get the following error. Can some one please help. How can I fix this error?

Error 1 Errors related to feature availability and configuration: Exceeded the maximum number of partitions in a measure group. The installed server editions allows not more than 3 partitions and not more than 1 writeback partition. 0 0

That is the error if you try to use Standard Edition and partitioning.

Partitioning is an EE-only feature.

_-_-_ Dave

Processes in Sysprocesses

Hi
When using sybase EA server I can utilise the following code to inform me of
what a particular Spid is executing
select s.stmtnum, s.linenum, o.name
from sysobjects o, master..sysprocesses s
where s.id = o.id
and s.spid = 77
is there anything I can use in sqlserver to give me the same kind of
details? ie, what line of a SP is running?
however, in sql server the columns linenum1) DBCC INPUTBUFFER(spid)
2) DECLARE @.Handle BINARY(30)
SELECT @.Handle = sql_handle
FROM master..sysprocesses
WHERE spid = @.@.spid
SELECT * FROM ::fn_get_sql(@.Handle)
For more details please refer to BOL
"almightygav" <almightygav@.discussions.microsoft.com> wrote in message
news:18CAF9A4-4F51-40F7-BC31-DF36FDE1B114@.microsoft.com...
> Hi
> When using sybase EA server I can utilise the following code to inform me
> of
> what a particular Spid is executing
> select s.stmtnum, s.linenum, o.name
> from sysobjects o, master..sysprocesses s
> where s.id = o.id
> and s.spid = 77
> is there anything I can use in sqlserver to give me the same kind of
> details? ie, what line of a SP is running?
> however, in sql server the columns linenum|||You might want to check this ... http://vyaskn.tripod.com/fn_get_sql.htm
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"almightygav" wrote:

> Hi
> When using sybase EA server I can utilise the following code to inform me
of
> what a particular Spid is executing
> select s.stmtnum, s.linenum, o.name
> from sysobjects o, master..sysprocesses s
> where s.id = o.id
> and s.spid = 77
> is there anything I can use in sqlserver to give me the same kind of
> details? ie, what line of a SP is running?
> however, in sql server the columns linenum|||Thanks for that Uri, every day is a lesson eh!
However, is there any way to drill down to a more granular level so that I
can identify what line of the proc it is running?
The problem we are encountering is that on occasions a couple of large
procedures grind to a standstill and we are trying to identify where they ar
e
slow.
Any more assistance would be gratefully received.
Gavin
"Uri Dimant" wrote:

> 1) DBCC INPUTBUFFER(spid)
> 2) DECLARE @.Handle BINARY(30)
> SELECT @.Handle = sql_handle
> FROM master..sysprocesses
> WHERE spid = @.@.spid
> SELECT * FROM ::fn_get_sql(@.Handle)
> For more details please refer to BOL
>
> "almightygav" <almightygav@.discussions.microsoft.com> wrote in message
> news:18CAF9A4-4F51-40F7-BC31-DF36FDE1B114@.microsoft.com...
>
>|||I've got to hand it to you Vadivel, that was good!
I'd like to think I could have written it if the help files were updated
inline with the Service Packs but hey ho, top job!
When do the help files get updated to show changes made by the Service
Packs? The two columns added to sysprocesses don't seem to be documented
anywhere?
Thank you very much, perfect!
"Vadivel" wrote:
> You might want to check this ... http://vyaskn.tripod.com/fn_get_sql.htm
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> http://thinkingms.com/vadivel
> "almightygav" wrote:
>|||> When do the help files get updated to show changes made by the Service
> Packs?
You need to download the update for Books Online. BOL update is not in the s
ervice pack files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"almightygav" <almightygav@.discussions.microsoft.com> wrote in message
news:FF370F16-5824-4E66-909F-2FFF2A1FB160@.microsoft.com...
> I've got to hand it to you Vadivel, that was good!
> I'd like to think I could have written it if the help files were updated
> inline with the Service Packs but hey ho, top job!
> When do the help files get updated to show changes made by the Service
> Packs? The two columns added to sysprocesses don't seem to be documented
> anywhere?
> Thank you very much, perfect!
> "Vadivel" wrote:
>|||Thanks almighty! I am glad i was of some help to you.
Btw as Tibor said, BOL updates are not part of Service pack files.
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"almightygav" wrote:
> I've got to hand it to you Vadivel, that was good!
> I'd like to think I could have written it if the help files were updated
> inline with the Service Packs but hey ho, top job!
> When do the help files get updated to show changes made by the Service
> Packs? The two columns added to sysprocesses don't seem to be documented
> anywhere?
> Thank you very much, perfect!
> "Vadivel" wrote:
>|||On Thu, 10 Nov 2005 05:22:04 -0800, "almightygav"
<almightygav@.discussions.microsoft.com> wrote:
>Thanks for that Uri, every day is a lesson eh!
>However, is there any way to drill down to a more granular level so that I
>can identify what line of the proc it is running?
>The problem we are encountering is that on occasions a couple of large
>procedures grind to a standstill and we are trying to identify where they a
re
>slow.
>Any more assistance would be gratefully received.
You can get that level of detail by running the profiler.
There's probably some voodoo way to get it via queries, but I don't
know it.
Well, maybe I do, "set statistics profile on" and then run your fat SP
and see if that helps, if that's not too much information!
J.

Process a Mining Model Programmatically

I have tried to process a data mining model that I have produce programmatically. I use the following code for processing

Private Sub BtProcess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtProcess.Click

' Now some different styles of processing

'Process mining structure first - don't process the models though

' ms.Process(ProcessType.ProcessStructure)

ms.Process(ProcessType.ProcessStructure)

DeclareBox.Text = "Process Data Mining Structure"

' Process the mining model only (because the structure has been processed above

' We could have said ProcessDefault without processing the structure first, though

mm.Process(ProcessType.ProcessDefault)

DeclareBox.Text = "Process Data Mining Model"

End Sub

I use the following code for data mining model and is correctly designed

Private Sub CreateMiningModel()

Dim mc As MiningModelColumn

mm = ms.MiningModels.Add(CbDatabase.Text & " miningmodelName") ', Utils.GetSyntacticallyValidID(miningmodelName, Type.GetType(MiningModel)))

mm.Algorithm = "Microsoft_Decision_Trees"

mm.AlgorithmParameters.Add("COMPLEXITY_PENALTY", 0.3)

mc = New MiningModelColumn("SUMCODE", "SUMCODE")

mc.SourceColumnID = ms.Columns("SUMCODE").ID

mc.Usage = "Key"

mm.Columns.Add(mc)

mc = New MiningModelColumn("STAFF_YES", "STAFF_YES")

mc.SourceColumnID = ms.Columns("STAFF_YES").ID

mc.Usage = "Input"

mm.Columns.Add(mc)

mc = New MiningModelColumn("D_G_OZODIS_IPER", "D_G_OZODIS_IPER")

mc.SourceColumnID = ms.Columns("D_G_OZODIS_IPER").ID

mc.Usage = "PredictOnly"

mm.Columns.Add(mc)

and I received the following error message. Any Ideas? What is wrong with this? Thank you in advance

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>test27</DatabaseID>
<MiningStructureID>test27 MiningStructureName</MiningStructureID>
<MiningModelID>test27 miningmodelName</MiningModelID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Mining Structure 'test27 MiningStructureName' completed successfully.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Processing Dimension 'test27 MiningStructureName ~MC-SUMCODE' failed.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Processing Dimension Attribute '(All)' completed successfully.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Processing Dimension Attribute 'OZOS NUM MANY' failed.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[VISIT].[OZOS_NUM_MANY] AS [VISITOZOS_NUM_MANY0_0]
FROM [VISIT] AS [VISIT]
Error Messages 1
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
Processing Dimension Attribute 'STAFF_YES' failed. 1 rows have been read.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
SQL queries 1
SELECT [VISIT].[STAFF_YES] AS [VISITSTAFF_YES0_0]
FROM [VISIT] AS [VISIT]
Error Messages 1
OLE DB error: OLE DB or ODBC error: Requested conversion is not supported.. Errors in the OLAP storage engine: An error occurred while the 'STAFF_YES' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.
Processing Dimension Attribute 'D_G_OZODIS_IPER' completed successfully.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Errors and Warnings from Response
OLE DB error: OLE DB or ODBC error: Requested conversion is not supported..
Errors in the OLAP storage engine: An error occurred while the 'STAFF_YES' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'test27 MiningStructureName ~MC-SUMCODE', Name of 'test27 MiningStructureName ~MC-SUMCODE' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'OZOS NUM MANY' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.
Errors in the high-level relational engine. The database operation was cancelled because of an earlier failure.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'test27 MiningStructureName ~MC-SUMCODE', Name of 'test27 MiningStructureName ~MC-SUMCODE' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'D_G_OZODIS_IPER' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.

Manolis

There seems to be an error converting the STAFF_YES values to the mining structure column type. What is the data type (in the database) for this column and what is the Mining Structure column type for this column?

|||

I thing that you are correct

In the database STAFF_YES is of type bit and in the mining structure is a text. I tried to convert the type in the mining structure to bit but I did not find such a type. How can I do that programmatically? Do I have to change anything to the database? I hope not

Thank you in advance

|||

I tried this code

mc = New MiningModelColumn("STAFF_YES", STAFF_YES, Type.GetType(MiningModelColumn)))

I received an error in MiningModelColumn, what is the problem with MiningModelColumn. Is there any other code?

Thank you in advance

Manolis

|||

Bit should work fine when represented (in the mining structure) as Boolean.

Can you try to change the structure column type to boolean and then retrain your model? The data type is specific to the mining structure column (not the mining model column)

|||

Thank you very much your advice worked perfectly.

My problem now is to get the type programmatically and not type OleDbType.Boolean by me

Can I try the following code?

Seems that does not work?

What do I have to use for the ScalarMiningStructureColumn

smsc = New ScalarMiningStructureColumn("D_G_OZODIS_IPER") , Utils.GetSyntacticallyValidID("VISIT"), Type.GetType(ScalarMiningStructureColumn)))

smsc.IsKey = False

smsc.Content = "Discrete"

smsc.KeyColumns.Add("VISIT", "D_G_OZODIS_IPER", OleDbType.Boolean)

ms.Columns.Add(smsc)

Thank you in advance

Manolis

Process 54 generated fatal exception c0000005 EXCEPTION_ACCESS_VIO

Hi..
I'm getting the following error when data is selected from views using
dynamic sql. Running SQL Server 2000 with SP3.
Process 54 generated fatal exception c0000005 EXCEPTION_A
CCESS_VIOLATION. SQL Server is terminating this process.
Thanks.
Search KB and make sure you are on current service pack. If problem persists, open a case with MS
Support (these types of errors are most often bugs in SQL Server).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"siddu" <siddu@.discussions.microsoft.com> wrote in message
news:F6112406-B866-4B2F-8DCB-87041C8D3E27@.microsoft.com...
> Hi..
> I'm getting the following error when data is selected from views using
> dynamic sql. Running SQL Server 2000 with SP3.
> Process 54 generated fatal exception c0000005 EXCEPTION_A
> CCESS_VIOLATION. SQL Server is terminating this process.
> Thanks.

Process 54 generated fatal exception c0000005 EXCEPTION_ACCESS_VIO

Hi..
I'm getting the following error when data is selected from views using
dynamic sql. Running SQL Server 2000 with SP3.
Process 54 generated fatal exception c0000005 EXCEPTION_A
CCESS_VIOLATION. SQL Server is terminating this process.
Thanks.Search KB and make sure you are on current service pack. If problem persists, open a case with MS
Support (these types of errors are most often bugs in SQL Server).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"siddu" <siddu@.discussions.microsoft.com> wrote in message
news:F6112406-B866-4B2F-8DCB-87041C8D3E27@.microsoft.com...
> Hi..
> I'm getting the following error when data is selected from views using
> dynamic sql. Running SQL Server 2000 with SP3.
> Process 54 generated fatal exception c0000005 EXCEPTION_A
> CCESS_VIOLATION. SQL Server is terminating this process.
> Thanks.

Process 54 generated fatal exception c0000005 EXCEPTION_ACCESS_VIO

Hi..
I'm getting the following error when data is selected from views using
dynamic sql. Running SQL Server 2000 with SP3.
Process 54 generated fatal exception c0000005 EXCEPTION_A
CCESS_VIOLATION. SQL Server is terminating this process.
Thanks.Search KB and make sure you are on current service pack. If problem persists
, open a case with MS
Support (these types of errors are most often bugs in SQL Server).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"siddu" <siddu@.discussions.microsoft.com> wrote in message
news:F6112406-B866-4B2F-8DCB-87041C8D3E27@.microsoft.com...
> Hi..
> I'm getting the following error when data is selected from views using
> dynamic sql. Running SQL Server 2000 with SP3.
> Process 54 generated fatal exception c0000005 EXCEPTION_A
> CCESS_VIOLATION. SQL Server is terminating this process.
> Thanks.

Procedure to search a table

Hi All,

I have the following table:

Code Block

Request
requestid
customername
age
sex
address
status

[status =
0 deleteme
1 addme
2 updatme

3 processed]

I need to write a stored proc that will do the following :

Input : requestid

Logic : check the customers who already have a status of 'deleteme' and have now filed a status of 'addme'. For this, inside the stored proc, i will retreive the customername and address using the requestid and then check in the table for all the customers who have the samename, address and status=0. How can i detect this.

Output : 0 for no entry found and requestid if a request of 'deleteme' for the same person was found.

For eg:

Code Block

requestid,customername,age,sex,address,status
22352,Jack,23,M,'Texas',1

I need to check if a person with the name of Jack and address Texas is there in the db with the status of 0.

Plz note: My Request table can contain more than 100000 records at a time. Please sugest accordingly

Plz help. Thanks

Here it is,

Code Block

Create Table #sample (

[requestid] int ,

[customername] Varchar(100) ,

[age] Varchar(100) ,

[sex] Varchar(100) ,

[address] Varchar(100) ,

[status] int

);

Insert Into #sample Values('20352','Jack','23','M','Texas','0');

Insert Into #sample Values('22352','Jack','23','M','Texas','1');

Insert Into #sample Values('22353','Jack','23','M','Austin','0');

Insert Into #sample Values('22362','Buck','23','M','Austin','0');

Insert Into #sample Values('22392','Buck','23','M','Austin','1');

select

*

from

#sample [add]

Where

Exists

(

select

[requestid]

,[customername]

,[address]

from

#sample [del]

where

[del].[status] = 0

and [del].[customername] = [add].[customername]

and [del].[address] = [add].[address]

and [del].[requestid] < [add].[requestid]

and [add].[status] = 1

)

|||

Hi,

Thanks for query. I ran the query and it returns all people who have status as 1.

I want all people who have status as 0 and not 1. How can i make this in a stored proc so that i accept only request id and then search.

thanks.

|||

Expected one.. Use the following query..

Code Block

select

*

from

#sample [add]

Where

Exists

(

select

[requestid]

,[customername]

,[address]

from

#sample [del]

where

[del].[status] = 1

and [del].[customername] = [add].[customername]

and [del].[address] = [add].[address]

and [del].[requestid] > [add].[requestid]

and [add].[status] = 0

)

|||

thanks so strange..i tried that previously on the query you gave first..it gave no records..no when i try yours, it gave the needed results..

nyways thanks.