Loss of inserted records during/after an insert

We have a system that records a data record for each cycle of a machine in an MS SQL Server database. These cycles take place approximately once every 10-12 seconds, and there are four stations on the machine, so we are writing approx. 24 records per min
ute. Our database contains four tables, one for each machine station. Each record contains a unique sequential number generated by the machine control software. Data is logged using SQL INSERT scripts in the application (Wonderware) that Operators use
to control the machine. (Wonderware script, BTW is not VBA, but is a proprietary scripting language.)
Everything works fine, UNTIL the one of the stations encounters an operational fault, and stops. This brings up a window on the control screen that requires the Operator to manually enter data, and an UPDATE statement is executed to modify the last recor
d generated. Occasionally when this update is processed, a single record will be lost (never written) in one or more of the data tables.
At first we had all of the records going to one table. Thinking maybe the update for one station was somehow locking an index in the table, we separated the tables so that each station has its own table. Since the station is stopped, no new record is ge
nerated for that station until after the update is processed. The other stations can still be running, so they are generating INSERT commands, which could coincide with the UPDATE command. Both commands use the same connection, which is always open.
We still occasionally lose ONE record in one or more of the other tables when the UPDATE executes.
Any thoughts?
Message posted via http://www.sqlmonster.com
Use the profiler and watch the sql statements - the most likely culprit is a
logic error within the application. Based on your narrative, I would guess
that the problem lies in the error-handling logic.
"Lee Drendall via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9981fa1e356140a298c4ffa13b629920@.SQLMonster.c om...
> We have a system that records a data record for each cycle of a machine in
an MS SQL Server database. These cycles take place approximately once every
10-12 seconds, and there are four stations on the machine, so we are writing
approx. 24 records per minute. Our database contains four tables, one for
each machine station. Each record contains a unique sequential number
generated by the machine control software. Data is logged using SQL INSERT
scripts in the application (Wonderware) that Operators use to control the
machine. (Wonderware script, BTW is not VBA, but is a proprietary scripting
language.)
> Everything works fine, UNTIL the one of the stations encounters an
operational fault, and stops. This brings up a window on the control screen
that requires the Operator to manually enter data, and an UPDATE statement
is executed to modify the last record generated. Occasionally when this
update is processed, a single record will be lost (never written) in one or
more of the data tables.
> At first we had all of the records going to one table. Thinking maybe the
update for one station was somehow locking an index in the table, we
separated the tables so that each station has its own table. Since the
station is stopped, no new record is generated for that station until after
the update is processed. The other stations can still be running, so they
are generating INSERT commands, which could coincide with the UPDATE
command. Both commands use the same connection, which is always open.
> We still occasionally lose ONE record in one or more of the other tables
when the UPDATE executes.
> Any thoughts?
> --
> Message posted via http://www.sqlmonster.com

Loss of inserted records during/after an insert

We have a system that records a data record for each cycle of a machine in an MS SQL Server database. These cycles take place approximately once every 10-12 seconds, and there are four stations on the machine, so we are writing approx. 24 records per minute. Our database contains four tables, one for each machine station. Each record contains a unique sequential number generated by the machine control software. Data is logged using SQL INSERT scripts in the application (Wonderware) that Operators use to control the machine. (Wonderware script, BTW is not VBA, but is a proprietary scripting language.)
Everything works fine, UNTIL the one of the stations encounters an operational fault, and stops. This brings up a window on the control screen that requires the Operator to manually enter data, and an UPDATE statement is executed to modify the last record generated. Occasionally when this update is processed, a single record will be lost (never written) in one or more of the data tables.
At first we had all of the records going to one table. Thinking maybe the update for one station was somehow locking an index in the table, we separated the tables so that each station has its own table. Since the station is stopped, no new record is generated for that station until after the update is processed. The other stations can still be running, so they are generating INSERT commands, which could coincide with the UPDATE command. Both commands use the same connection, which is always open.
We still occasionally lose ONE record in one or more of the other tables when the UPDATE executes.
Any thoughts?
--
Message posted via http://www.sqlmonster.comUse the profiler and watch the sql statements - the most likely culprit is a
logic error within the application. Based on your narrative, I would guess
that the problem lies in the error-handling logic.
"Lee Drendall via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9981fa1e356140a298c4ffa13b629920@.SQLMonster.com...
> We have a system that records a data record for each cycle of a machine in
an MS SQL Server database. These cycles take place approximately once every
10-12 seconds, and there are four stations on the machine, so we are writing
approx. 24 records per minute. Our database contains four tables, one for
each machine station. Each record contains a unique sequential number
generated by the machine control software. Data is logged using SQL INSERT
scripts in the application (Wonderware) that Operators use to control the
machine. (Wonderware script, BTW is not VBA, but is a proprietary scripting
language.)
> Everything works fine, UNTIL the one of the stations encounters an
operational fault, and stops. This brings up a window on the control screen
that requires the Operator to manually enter data, and an UPDATE statement
is executed to modify the last record generated. Occasionally when this
update is processed, a single record will be lost (never written) in one or
more of the data tables.
> At first we had all of the records going to one table. Thinking maybe the
update for one station was somehow locking an index in the table, we
separated the tables so that each station has its own table. Since the
station is stopped, no new record is generated for that station until after
the update is processed. The other stations can still be running, so they
are generating INSERT commands, which could coincide with the UPDATE
command. Both commands use the same connection, which is always open.
> We still occasionally lose ONE record in one or more of the other tables
when the UPDATE executes.
> Any thoughts?
> --
> Message posted via http://www.sqlmonster.com

Loss of inserted records during/after an insert

We have a system that records a data record for each cycle of a machine in a
n MS SQL Server database. These cycles take place approximately once every
10-12 seconds, and there are four stations on the machine, so we are writing
approx. 24 records per min
ute. Our database contains four tables, one for each machine station. Each
record contains a unique sequential number generated by the machine control
software. Data is logged using SQL INSERT scripts in the application (Wond
erware) that Operators use
to control the machine. (Wonderware script, BTW is not VBA, but is a proprie
tary scripting language.)
Everything works fine, UNTIL the one of the stations encounters an operation
al fault, and stops. This brings up a window on the control screen that req
uires the Operator to manually enter data, and an UPDATE statement is execut
ed to modify the last recor
d generated. Occasionally when this update is processed, a single record wi
ll be lost (never written) in one or more of the data tables.
At first we had all of the records going to one table. Thinking maybe the u
pdate for one station was somehow locking an index in the table, we separate
d the tables so that each station has its own table. Since the station is s
topped, no new record is ge
nerated for that station until after the update is processed. The other sta
tions can still be running, so they are generating INSERT commands, which co
uld coincide with the UPDATE command. Both commands use the same connection,
which is always open.
We still occasionally lose ONE record in one or more of the other tables whe
n the UPDATE executes.
Any thoughts?
Message posted via http://www.droptable.comUse the profiler and watch the sql statements - the most likely culprit is a
logic error within the application. Based on your narrative, I would guess
that the problem lies in the error-handling logic.
"Lee Drendall via droptable.com" <forum@.droptable.com> wrote in message
news:9981fa1e356140a298c4ffa13b629920@.SQ
droptable.com...
> We have a system that records a data record for each cycle of a machine in
an MS SQL Server database. These cycles take place approximately once every
10-12 seconds, and there are four stations on the machine, so we are writing
approx. 24 records per minute. Our database contains four tables, one for
each machine station. Each record contains a unique sequential number
generated by the machine control software. Data is logged using SQL INSERT
scripts in the application (Wonderware) that Operators use to control the
machine. (Wonderware script, BTW is not VBA, but is a proprietary scripting
language.)
> Everything works fine, UNTIL the one of the stations encounters an
operational fault, and stops. This brings up a window on the control screen
that requires the Operator to manually enter data, and an UPDATE statement
is executed to modify the last record generated. Occasionally when this
update is processed, a single record will be lost (never written) in one or
more of the data tables.
> At first we had all of the records going to one table. Thinking maybe the
update for one station was somehow locking an index in the table, we
separated the tables so that each station has its own table. Since the
station is stopped, no new record is generated for that station until after
the update is processed. The other stations can still be running, so they
are generating INSERT commands, which could coincide with the UPDATE
command. Both commands use the same connection, which is always open.
> We still occasionally lose ONE record in one or more of the other tables
when the UPDATE executes.
> Any thoughts?
> --
> Message posted via http://www.droptable.comsql

Losing odbc connection on install

We have a system which uses an ODBC connection to connect to SQL Server 2000
for Ceridian Prism--an application for HR departments. Now we are installing
a VB.Net application which uses MSDE (SQL 7). The problem is that for some
reason we are losing the original ODBC connection to 2000 when we install
MSDE. Now I realize that installing MSDE 2000 may help this issue, however
we really need to use SQL 7 for now. Does anyone have any ideas of what
might be causing the loss in the connection? Is the SQL 7 install
overwriting something that the ODBC needs for the 2000 connection? Is it
something with named instances? We are using the standard MSDE installation
from Microsoft.
Thanks.
My guess is that the MSDE 7 installation is installing an older version of
MDAC which is not ADO.Net compatible. It could also be an issue with named
instances as earlier version of MDAC (pre 2.5 I think) did not support named
instances.
Jim
"LisaConsult" <lisaconsult@.online.nospam> wrote in message
news:81EA76DC-A07B-4982-B9F2-CD31ACE1F0B0@.microsoft.com...
> We have a system which uses an ODBC connection to connect to SQL Server
> 2000
> for Ceridian Prism--an application for HR departments. Now we are
> installing
> a VB.Net application which uses MSDE (SQL 7). The problem is that for
> some
> reason we are losing the original ODBC connection to 2000 when we install
> MSDE. Now I realize that installing MSDE 2000 may help this issue,
> however
> we really need to use SQL 7 for now. Does anyone have any ideas of what
> might be causing the loss in the connection? Is the SQL 7 install
> overwriting something that the ODBC needs for the 2000 connection? Is it
> something with named instances? We are using the standard MSDE
> installation
> from Microsoft.
> Thanks.
|||Thanks for your response. Actually, we know that it is somehow SQL Server
related and not MDAC because once we uninstalled Server Manager and MSDE, the
connection worked fine again. As an aside, if they needed MDAC, we installed
2.6, but as I said, I don't believe this was the issue. Any other thoughts?
Thanks
"Jim Young" wrote:

> My guess is that the MSDE 7 installation is installing an older version of
> MDAC which is not ADO.Net compatible. It could also be an issue with named
> instances as earlier version of MDAC (pre 2.5 I think) did not support named
> instances.
> Jim
> "LisaConsult" <lisaconsult@.online.nospam> wrote in message
> news:81EA76DC-A07B-4982-B9F2-CD31ACE1F0B0@.microsoft.com...
>
>
|||Oops, my mistake, this app is actually still a VB6 app.
"LisaConsult" wrote:

> We have a system which uses an ODBC connection to connect to SQL Server 2000
> for Ceridian Prism--an application for HR departments. Now we are installing
> a VB.Net application which uses MSDE (SQL 7). The problem is that for some
> reason we are losing the original ODBC connection to 2000 when we install
> MSDE. Now I realize that installing MSDE 2000 may help this issue, however
> we really need to use SQL 7 for now. Does anyone have any ideas of what
> might be causing the loss in the connection? Is the SQL 7 install
> overwriting something that the ODBC needs for the 2000 connection? Is it
> something with named instances? We are using the standard MSDE installation
> from Microsoft.
> Thanks.
|||I still think that it is a problem with the data connection layer and not
SQL Server. Have you tried installing MDAC 2.8 after MSDE 7 is installed.
Jim
"LisaConsult" <lisaconsult@.online.nospam> wrote in message
news:2271E92F-AFE5-4C14-A8FA-2A05326227B6@.microsoft.com...[vbcol=seagreen]
> Thanks for your response. Actually, we know that it is somehow SQL Server
> related and not MDAC because once we uninstalled Server Manager and MSDE,
> the
> connection worked fine again. As an aside, if they needed MDAC, we
> installed
> 2.6, but as I said, I don't believe this was the issue. Any other
> thoughts?
> Thanks
> "Jim Young" wrote:

Loosing primari keys when system crash

Hello,
When we our MS-SQL server has system crash a lot of all our primair keys are lost.
Some time even fields are lost.
We are running MS-SQL server on a cluster and on a stand alone server
Dos some one no the problem and nows a solution.What kind of the backup strategy do you use?

Looping through non system database objects using SMO and VB.net

Hi,

We are using SMO to compare objects in our SQL Server database with another instance of sql server. I'm able to loop thourgh the stored procedures with no problem and retreave the names of them however it loops through all of the SPs even the system ones. This makes the loop take a while since it has to cycle through all of the system stored procedures. Is there a way to loop through only the dbo sps? I'm using VB.net

For Each sp In theserver.Databases.Item(DBName).StoredProcedures

x = sp.ToString

If sp.IsSystemObject = False Then

'MsgBox(x)

End If

Next

Thanks

Hi,

see this here:

http://www.sqlteam.com/item.asp?ItemID=23185

The following can be used to test your code against your databases:

For one of my databases with 902 Procedures this was a huge difference.

using System;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.Win32;

namespace SMOProject

{

class Program

{

static void Main(string[] args)

{

EvaluateSMOTime(".", "SQLSErver2005", true);

EvaluateSMOTime(".", "SQLSErver2005", false);

Console.ReadLine();

}

internal static void EvaluateSMOTime(string ServerName, string DatabaseBaseName, bool SetDefaultField)

{

DateTime Before = DateTime.Now;

Server theServer = new Server(ServerName);

if (SetDefaultField)

theServer.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");

Database myDB = theServer.Databases[DatabaseBaseName];

foreach (StoredProcedure sp in myDB.StoredProcedures)

{

if (!sp.IsSystemObject)

{

Console.Write(".");

}

}

DateTime After = DateTime.Now;

TimeSpan Diff = After.Subtract(Before);

Console.WriteLine(string.Format("With{1} tweaking the DefaultInitFields : {2} ms", Diff.Milliseconds, SetDefaultField ? string.Empty : "on"));

}

}

}

HTH, Jens K. Suessmeyer.

http://www.sqlserver20005.de

LOOKUPCUBE Problem!

Hi,

Dose LOOKUPCUBE function include "Customer Member" ?
I use Customer Member in LOOKUPCUBE syntax and system response #Err!
So, LOOKUPCUBE dosen't support Customer Member?
Thanks for any advice!

Angi

Hi Angi,

The LookupCube function returns either a numeric expression or a string expression. Assuming you have a Customer dimension with a hierarchy called Customers (with levels Country-State-City-Name), and you are issuing the query in cube Budget, but want to evaluate these expressions in another cube called Sales, here are two working examples:

with member x as 'lookupcube("Sales", "[Customers].[Country].&[Canada].name")'
select x on 0 from [Budget] -- returns the string Canada

with member x as 'lookupcube("Sales", "[Customers].[City].count")'
select x on 0 from [Budget] -- returns the number of cities in the City level

Hope this helps,

Artur

|||

Artur,

Thanks for help!
My expression as follow...

WITH
MEMBER [X].[XX02].[THISPERIOD] AS '[X].[XX02].[200612]'
MEMBER [IV].[IV02].[Execute] AS 'LOOKUPCUBE ("CUBEEF" , " (
[X].[XX08].&[XX0830010] , " + [X].[XX04].CURRENTMEMBER.UNIQUENAME + "," +
[X].[XX01].CURRENTMEMBER.UNIQUENAME + " , [X].[XX02].[THISPERIOD] )" ) '
....
....

So, the Customer Member is [X].[XX02].[THISPERIOD] and the
[IV].[IV02].[Execute] will response #Err.
Any idea?

Angi

|||

The second parameter in the LookupCube function call is invalid. To see the detailed error message, please double click on the Err# cell in SQL Management Studio and it will display the reason. Looks like you are trying to pass a set to the function and not a string. What are you trying to achieve with this query?

--Artur

lookup to check values in 13 columns with same values

hi,

it is my first post on this forum, please be patient if i miss any important bit of information.

i am transporting data from a legacy system into mssql 2k5 using SSIS.
among those column of a dataset there are 13 columns, all necessary for operational reasons, that i need to ensure data consistance.

i believe i could do this check using the lookup data flow item, but surely there must be a way to do it in a more streamlined fashion.

since column names contain numbers to distinguish the version, eg; col01, col02, col03 .. col13.

i thought i could include the lookup within a loop and use a couple of variables to do this trick, but since i have not done it before i am asking for some sort of guidance from a guru among you folks.

please let me know if further clarification is necessary.

regards,

nicolas

If you are trying to confirm that the 13 values for each row in the source match the corresponding row in the destination, then a lookup is the way to go. Using a single lookup with the 13 columns all mapped makes sense. Trying to do something dynamic is not going to work, and even if it did this would mean 13 individual lookup calls, so probably a lot slower due to the extra overhead.

Another method when you have lots of columns to compare is to use a hash or checksum across the columns. You can compute this checksum in the pipeline on the incoming source, and compare that against the checksum stored in the destination. This means the lookup is only one column, so for wide column data and lots of them it will be faster due to the reduced data being transferred, and it is easier to just select columns for checksum generation compared to mapping in the lookup. This does mean that your destination needs to be expaned to store the checksum.

A checksum transform is available here (http://www.sqlis.com/default.aspx?21) or you can implement your own in the Script Component.

|||darren,

i understand your point about looking through 13 columns been slow, but i am not certain if i understand how to hash all those columns to compare their values.

for clarification, i need to map or rather insure that those values in all 13 columns will comply with a foreign key constraint, thus i am ensuring that only values listed in my reference table exist in those columns.

would this scenario allow me to use hash for comparing values?

many thanks|||

I may have misunderstood this. If the 13 columns are the key for one table, then the hash concept will work. the hash serves as a compund key for those 13 columns,. If they are 13 different reference tables, then i messed up, you woudl use 13 lookups, one to check each table, probably what you meant first time around. There is no shortcut for these 13 lookups if that is what you require.

I digress a bit, but a good strategy can be to just load the data and let it fail on the FK violation. Set the component to re-direct any failed rows to the error output. You may then need to do your lookups or whatever you want to do when you have missing reference data. This can be much faster than lookups for every row. If 100 rows are loaded, but only 1 is a new key, the error output will get 1 row. This means 1 lookup (fix work) as opposed to 100.

|||

Hi Darren,

In the example - you have assumed that the base tables contain the Foreign Key column values - What if we need to lookup on a secondary table, Obtain the primary key from the secondary table and populate them in the base table column.

Is it beneficial to handle these relations in SQL JOIN Statements while fetching records from the source tables rather than doing the lookup using LookUP Components in the SSIS pipeline (which I believe works at record level - meaning a bulk lookup is impossible.)

Please share your comments.

Thanks,
Loonysan

|||Ok,

since i have similar situation with 4 other dataset, i would like to probe this idea further as well as maek sure that i clarify it so we understand this situation properly.

this source dataset that i extracted from our legacy data source has these 13 columns (col1..col13) among many.

in on our destination system, i created a table (lookup table) which will enable me to map and transform legacy codes into relevant new codes in our destination table.

the dataset is intented to be inserted into a table that has 13 foreign keys (one for each of those 13 columns) which references another table, thus ensuring that only new codes will be inserted in any of those 13 columns.

having said that, you believe that i should join the lookup table, try to insert into the destination table & catch any error?

many thanks

Lookup table

I have a situation where I need to use the following logic. If an item is non-stocking at LOC1, the system checks LOC4 first to see if it is stocking at that location, LOC2 second, LOC3 third, etc. If no stocking LOC is found, the records remain unmodified.

Stocking LOC (in order of precedence)
Non-Stocking LOC 1 2 3 4 5

LOC1 LOC4 LOC2 LOC3 LOC5
LOC2 LOC4 LOC1 LOC2 LOC5
LOC3 LOC5 LOC4 LOC1 LOC2
LOC4 LOC1 LOC3 LOC2 LOC5
LOC5 LOC3 LOC4 LOC1 LOC2
LOC6 LOC4 LOC2 LOC1 LOC5 LOC3
Right now we have a whole series of IF statements to work through this logic. When we add a location, a lot of code needs to be changed. Is there a way to build this into a table and be able to do the lookup more effectively, and that would make it easier to add a location?

Thanks in advance.I'd propose a more normalized table with 3 fields:
Non_stocking_loc, stocking_loc, precedence

Given a non-stocking loc, it's easy to retrieve all stocking locs for it, ordered by precedence.

Lookup concurrency issue in packages running simultaneously in parallel

I have a system of SSIS packages in which several packages perform the same lookup on the same table. E.g., i have PackageA, PackageB and PackageC all doing a lookup on TableA. All of these packages are spawned by the same PackageD and run frequently. In some cases, there is an issue with concurrency on these lookups. I get the following exception :

"

The ProcessInput method on component "LKP Lookup SecurityID" (6658) failed with error code 0xC004702C. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

"

The hex code of this exception corresponds to the following description : "DTS_E_BUFFERNOTLOCKED. This buffer is not locked and cannot be manipulated." That's as much as i could find on this.

My suspision is that the SSIS engine somehow figures that the lookup in these distinct packages is the same one and builds a shared version of the lookup table in memory. Then there is some sort of a multi-threading issue in accessing this shared memory which leads to the exception above.

Has anyone experienced this? Can someone shed some light on this?

Thanks a lot

-Alex

Just out of curiosity, did you copy-n-paste the lookups across the packages?

Are you using SQL in the lookup (you should be!) or are you simply choosing the table?|||

Phil,

i used a template package that had the lookup defined. I generated a new package ID for each of the derived packages.

i am using the default setup for the lookup, without messing with SQL. The table is very small (<300 rows) and is loaded into memory entirely. What advantage would i get from modifying SQL? Thanks

|||

chianuri wrote:

Phil,

i used a template package that had the lookup defined. I generated a new package ID for each of the derived packages.

i am using the default setup for the lookup, without messing with SQL. The table is very small (<300 rows) and is loaded into memory entirely. What advantage would i get from modifying SQL? Thanks

Well, I'm just curious to know if you keep things set up the way you do, but RECREATE each lookup (sounds like it would be a quick task) in each package, what happens then?

As far as using SQL, it is always preferred to use SQL in any component, if possible. Numerous reasons, but the two primary ones are so that you can select just the columns you need, and can filter the rows ahead of time using the power of the SQL Server engine versus SSIS.

For your issue, I was just curious to know what happens if you replace the table selection with SQL. If the same thing happens, then what happens if you alias the lookup table differently in each lookup. (select ... from table a ; select ... from table b ; etc...)|||

chianuri wrote:

Phil,

i used a template package that had the lookup defined. I generated a new package ID for each of the derived packages.

The fact that you are using a template and your data-flow exists in that template makes me *very* suspicious. Perhaps there is a handle that is replicated across those multiple packages. It could be that you have stumbled upon a bug.

Would it be possible for you to post a repro package? Perhaps one that works (i.e. fails) when run against AdventureWorks?

Thanks

Jamie

|||

Phil Brammer wrote:

As far as using SQL, it is always preferred to use SQL in any component, if possible. Numerous reasons, but the two primary ones are so that you can select just the columns you need, and can filter the rows ahead of time using the power of the SQL Server engine versus SSIS.

Phil is absolutely correct. Don't assume that selecting from the dropdown is the same as issuing a "SELECT * FROM...". As explained here:

SELECT *... or select from a dropdown in an OLE DB Source component?

http://blogs.conchango.com/jamiethomson/archive/2006/02/21/SSIS_3A00_-SELECT-_2A002E002E002E00_-or-select-from-a-dropdown-in-an-OLE-DB-Source-component_3F00_.aspx

-Jamie

|||

Phil Brammer wrote:

Well, I'm just curious to know if you keep things set up the way you do, but RECREATE each lookup (sounds like it would be a quick task) in each package, what happens then?

Actually, it's not going to be that easy in my case as i have a fairly large number of packages in total ( >30), and many of them share lookups. I just gave an oversimplified account of the system in my original description of the problem.

Phil Brammer wrote:

As far as using SQL, it is always preferred to use SQL in any component, if possible. Numerous reasons, but the two primary ones are so that you can select just the columns you need, and can filter the rows ahead of time using the power of the SQL Server engine versus SSIS.

For your issue, I was just curious to know what happens if you replace the table selection with SQL. If the same thing happens, then what happens if you alias the lookup table differently in each lookup. (select ... from table a ; select ... from table b ; etc...)

This suggestion seems to be more manageable in my case (as i won't need to recertify all the metadata downstream from the lookup once i replace it). I'll keep you posted on what happens. I'm still curious as to what exactly causes the error in the first place.

I'm wondering if SP2 fixes this problem since it does fix another issue with the lookup component...

I'd appreciate if anyone else who has experienced anything of this sort or has more details on 0xC004702C (DTS_E_BUFFERNOTLOCKED) responded to this post.

|||

Jamie Thomson wrote:

chianuri wrote:

Phil,

i used a template package that had the lookup defined. I generated a new package ID for each of the derived packages.

The fact that you are using a template and your data-flow exists in that template makes me *very* suspicious. Perhaps there is a handle that is replicated across those multiple packages. It could be that you have stumbled upon a bug.

Would it be possible for you to post a repro package? Perhaps one that works (i.e. fails) when run against AdventureWorks?

Thanks

Jamie

Thanks for the reply Jamie. I'll put something together and post it, but it is difficult to replicate this bug since it happens non-deterministically (at least it looks that way on the surface).

|||

chianuri wrote:

Thanks for the reply Jamie. I'll put something together and post it, but it is difficult to replicate this bug since it happens non-deterministically (at least it looks that way on the surface).

I'll see if I can recreate it as well.

If you need somewhere to post your package, you can contact me or very likely Jamie as well. (Don't want to volunteer him outright! )

I know we are all interested to know if this is a bug and if so, is there a workaround.

Thanks,
Phil|||

Phil Brammer wrote:

chianuri wrote:

Thanks for the reply Jamie. I'll put something together and post it, but it is difficult to replicate this bug since it happens non-deterministically (at least it looks that way on the surface).

I'll see if I can recreate it as well.

If you need somewhere to post your package, you can contact me or very likely Jamie as well. (Don't want to volunteer him outright! )

I know we are all interested to know if this is a bug and if so, is there a workaround.

Thanks,
Phil

Thanks for your replies, Phil.

I started converting my lookups to SELECTs, as both you and Jamie suggested and will post the results here.

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

|||

chianuri wrote:

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

And I've raised this issue before, but was told that even though the tasks/components have the same GUID, the package's GUID is different and hence should ensure uniqueness. I've always been skeptical of that claim. So pretty much since then, I have not done much copy-n-pasting.|||

Phil Brammer wrote:

chianuri wrote:

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

And I've raised this issue before, but was told that even though the tasks/components have the same GUID, the package's GUID is different and hence should ensure uniqueness. I've always been skeptical of that claim. So pretty much since then, I have not done much copy-n-pasting.

Yes, this is exactly what i'm talking about! I knew someone must've experienced this problem before....

Would you happen to have an update on that request from the SSIS team? Are they planning for a hotfix anytime soon?

|||

chianuri wrote:

Phil Brammer wrote:

chianuri wrote:

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

And I've raised this issue before, but was told that even though the tasks/components have the same GUID, the package's GUID is different and hence should ensure uniqueness. I've always been skeptical of that claim. So pretty much since then, I have not done much copy-n-pasting.

Yes, this is exactly what i'm talking about! I knew someone must've experienced this problem before....

Would you happen to have an update on that request from the SSIS team? Are they planning for a hotfix anytime soon?

No. No update because they don't feel there's an issue, until now, perhaps. You should visit my Connect submission and post your feedback, with detailed notes.

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

Phil Brammer wrote:

chianuri wrote:

Phil Brammer wrote:

chianuri wrote:

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

And I've raised this issue before, but was told that even though the tasks/components have the same GUID, the package's GUID is different and hence should ensure uniqueness. I've always been skeptical of that claim. So pretty much since then, I have not done much copy-n-pasting.

Yes, this is exactly what i'm talking about! I knew someone must've experienced this problem before....

Would you happen to have an update on that request from the SSIS team? Are they planning for a hotfix anytime soon?

No. No update because they don't feel there's an issue, until now, perhaps. You should visit my Connect submission and post your feedback, with detailed notes.

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

done... hope they respond to it

|||

I hope they respond. This definately looks like a bug.

I've never liked the fact that they don't generate new IDs for all the package objects even though I didn't have a real good reason for it. it just didn't "smell" very good. How hard is it to loop over the executables collection and components collections and change all the IDs?

Good spot guys!! Although shame we can't get a repro.

-Jamie

Looking for 'upsizing' from Access/PivotCharts

Hi All!

I'm doing analysis of system performance metrics recorded from a web application performance test. I have approximately 15 tables, each with unique system and test metrics, ranging in size from 6 thousand to 6 million rows. E.g:

iostats: time, host, disk, read_bps, write_bps, ...
netstats: time, host, active_conns, xmit_errs, resets, ...
teststats: time, test_name, virtual_users, ...
timerstats: time, script_name, timer_name, elapsed_time, ...

Last year i used MS Access + pivot tables to do the analysis of this information, but Access isn't cutting it this year. It frequently goes out to lunch and never returns whenever I try to build the charts i'm looking for (most of which use PivotChart views)

I've spent a few days trying to get my head around using SSAS for this purpose, but have run into a few stumbling blocks

1 - The information in the 15 tables is loosely correlated on time of day, some have one sample per second, others one sample every thirty seconds, and still others are whenever an update decides to roll in. I'm having a very difficult time figuring out how to make a usable 'time' dimension that i can use to chart the information.

2 - I seem to constantly run into 'missing attribute value' errors when trying to build and deploy my analysis services projects that contain cubes. Frequently this seems to be due to cases where i don't have specific information on a specific dimension (e.g. host / cpu / etc) for a given time period.

3 - I'm not sure how to visualize some of the output from the cube interactions that do work. I can get a table working in the cube browser, but how do i chart that? Is the Excel AS plugin the only approach available? I tried to view the tables via access linked tables, but a) Access (2002) complains it can't modify tables against SQL 2005, and b) Access doesn't pull the entire set of data back, just the first 50k records or so.

4 - If i want to calculate disk utilization on a per disk per host basis, would that be a valid use of a dimension hierarchy?

I'm kind of running out of time. My game plan for today is to recalculate all tables to exactly one minute sample intervals and try again, hoping that the reduced row count will make things a little more smooth.

Thanks for any advice!!!

Bob

Several ideas for you:

Try to see if Office 2007 beta is going to work for you. Install Office2007 beta and try if you can fit your data sizes there.

Second. If you are using Analysis Services Excel add-in is not the only choice. You can use Excel Pivot Tables to connect to Analysis Services. You can use many other client applications to diplay Analysis Services data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Hi Edward!

Thanks for the response. I may take a look at office 2007 today to see how it goes.

looking for non similar orders

System is "MFG" Custom jobs.
Tracking customer's last order and looking for changes they make in the job
detail table as compared to order before.
Table schema:
OrderID, line#, Item, QtyReq
I need to verify the last order and if it changed with the clients prior
order.
I have 6 line items per order, and the rows 1,2,3 are the critical check
point I need to verify.
If it returned int for # of rows diff that would be fine.
http://www.webhost4life.com/hosting.asp shows monthly fees for 5,10,20 USD
per month.Hi
Check out http://www.aspfaq.com/etiquett_e.asp?id=5006 on how to post DDL
and example data. It is also useful to post your current attempt and the
information you expect from the example data.
John
"Stephen Russell" wrote:

> System is "MFG" Custom jobs.
> Tracking customer's last order and looking for changes they make in the jo
b
> detail table as compared to order before.
> Table schema:
> OrderID, line#, Item, QtyReq
> I need to verify the last order and if it changed with the clients prior
> order.
> I have 6 line items per order, and the rows 1,2,3 are the critical check
> point I need to verify.
> If it returned int for # of rows diff that would be fine.
> http://www.webhost4life.com/hosting.asp shows monthly fees for 5,10,20 US
D
> per month.
>
>
>
>