Loss of Connection

We have an application running on a server that does a connection
check on it's connection to the database on a SQL Server 2000.
Sometimes it looses it's connection and then is unable to restablish
the connection for over an hour. During the time that it looses
contact with the SQL Server there is some pretty heavy activity on the
SQL Server 2000 box.
Is there some setting I've overlooked ... or is this some weakness on
the part of SQL Server ? I don't think the application is doing a
query or anything, I think it's just some heartbeat kind of routine.
rls
Seattle, WAare you attaching via name or IP address? Without a WINS, DNS or ADS server,
attaching via name may be unrealizable. Try using the IP address.
--
J
www.urbanvoyeur.com
"brlarue" <ron.strouss@.westfarm.com> wrote in message
news:42b547894434e770528406949d17c5b5@.news.teranews.com...
> We have an application running on a server that does a connection
> check on it's connection to the database on a SQL Server 2000.
> Sometimes it looses it's connection and then is unable to restablish
> the connection for over an hour. During the time that it looses
> contact with the SQL Server there is some pretty heavy activity on the
> SQL Server 2000 box.
> Is there some setting I've overlooked ... or is this some weakness on
> the part of SQL Server ? I don't think the application is doing a
> query or anything, I think it's just some heartbeat kind of routine.
> rls
> Seattle, WA|||We have a DNS. I'll take a look at the possibility of using the IP
address. Here is the message coming from the application that looses
it's connection.
GENTRAN Notification: ConvertedNotification3 Oct 05 2003 07:29:20
EventID=55867 1-1-50009:ODBC: MFC database exception in
Program/RETCODE: Edimgr/-1State:08S01,Native:0,Origin:[Microsoft][ODBC
SQL Server Driver]
Communication link failure
-
On Mon, 6 Oct 2003 06:26:59 -0400, "UrbanVoyeur" <nospam@.nospam.com>
wrote:
>are you attaching via name or IP address? Without a WINS, DNS or ADS server,
>attaching via name may be unrealizable. Try using the IP address.

Losing record after synchronizing subscriptions

Hi

We got a server running SQL server 2000 SP4 with a database that’s being replicated

There are 17 subscribers running MSDE SP4 using merge replication. Replication is started manualy

Initially we tested this with two subscriptions an everything went well, but now, since 3 months, we are facing a weird problem while sync'ing. We have massive data loss on records that where inserted at the subscribers. Records seem to disappear, but only record that have a foreign key constraint. What I mean is that for example a record is inserted at the table that holds our client records with primary key ‘ClientID’ and then a record is inserted in a table with actions for that client with a foreign key ‘ClientID’ referring to the client table. After sync’ing that client record is inserted correctly in database on the publisher but the records in the table with actions are gone.

As far as I know the tables are correctly formed with identity set not for replication and so on.

Shortly, I can’t find any problem, a specially when it doesn’t happen always.

If anyones has faced this and got a solution, please let me know.

Thanks.

Raf

Are there constraint violations that are happening?

Also look at the article property compensate_for_errors. it is turned ON by default. What this does is, say for an insert coming from publisher to subscriber, if there is a constraint violation or some other failure, a delete is sent back to the publisher resulting in data loss.

Set this propery to false and monitor your system for data integrity.

|||

Thanks

I'll check this out

Losing it again - how to pass value to sp?

I cannot remember how to pass a value to a stored procedure. I would work this through but I am really running out of time, any help greatly appreciated. This is my stored procedure and I need to pass CompanyID from the code behind page in for the stored procedure @.C_ID value.

PROCEDURE dbo.EditCompanyInfo
@.C_ID int,
@.CS_CompanyName nchar(100),
@.CS_City nchar(50)

AS
UPDATE tblCompanyInfo_Submit
SET CS_CompanyName = @.CS_CompanyName, CS_City = @.CS_City
WHERE C_ID = @.C_ID
RETURN

This is my aspx. page:

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server" >
<asp:TextBox ID="TextBox1" runat="server"> </asp:TextBox> <br />
<asp:TextBox ID="TextBox2" runat="server"> </asp:TextBox> <br />
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br />
<asp:DetailsView ID="DetailsView1" runat="server" DataSourceID="SqlDataSource2" Height="50px"
Width="125px">
<Fields>
<asp:CommandField ShowEditButton="True" ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"
InsertCommand="CompanyInfoSubmit" InsertCommandType="StoredProcedure" OnInserted="SqlDataSource2_Inserted"
SelectCommand="SELECT CS_CompanyName, CS_City FROM tblCompanyInfo_Submit WHERE C_ID = @.CompanyID "
UpdateCommand="EditCompanyInfo" UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:Parameter Name="CS_CompanyName" Type="String" />
<asp:Parameter Name="CS_City" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Direction="ReturnValue" Name="ReturnValue" Type="Int32" />
<asp:Parameter Name="CS_CompanyName" Type="String" />
<asp:Parameter Name="CS_City" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
<br />
</asp:Content>

CODE BEHIND:

public partial class aaatest : System.Web.UI.Page
{
int CompanyID;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
DetailsView1.ChangeMode(DetailsViewMode.Insert);
TextBox1.Text = "insert";
TextBox3.Text = Convert.ToString(CompanyID);
}
}
protected void SqlDataSource2_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
{
foreach (System.Data.SqlClient.SqlParameter param in e.Command.Parameters)
{
string RValue = Server.HtmlEncode(param.Direction.ToString());
if ( RValue == "ReturnValue" && Page.IsPostBack)
{
TextBox1.Text = Server.HtmlEncode(param.Value.ToString());
TextBox2.Text = "Return";
CompanyID = Convert.ToInt16(TextBox1.Text);
TextBox3.Text = Convert.ToString(CompanyID);
}
}
}
}
}

This might help... although you'll need to adjust it to be UpdateParameters on an update etc.

SqlDataSource1.SelectParameters["ParamName"] =

newParameter("ParamName",TypeCode.String,"Value");sql

Loosing SQL connection while running ASP

We are running an ASP application. That is fairly simple.

It selects from one table (15.000 records), and updates into another table.
But when we execute the ASP script, it reports the following error, after
completing between 70%-80%. This varies each time it runs

"Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied. "

The application is running in ths following server environment

Server 1

Fujitsu-Siemens RX300 Server (Dual 2,8 Xeon, 1 Gb Ram, 72 Gb SCSI Raid 1)

Windows 2003 Web Server (IIS)

Server 2

Fujitsu-Siemens RX300 Server (Dual 2,8 Xeon, 1 Gb Ram, 72 Gb SCSI Raid 1)

Windows 2003 Standart server

Microsoft SQL Server 2000Jesper Carstensen (jesper@.swush.com) writes:
> We are running an ASP application. That is fairly simple.
> It selects from one table (15.000 records), and updates into another
> table. But when we execute the ASP script, it reports the following
> error, after completing between 70%-80%. This varies each time it runs
>
> "Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
> denied. "

So how exactly do you retrieve the rows and submit the updates? For
retrieval I would recommend client-side cursors.

Is the table you update on the same server as the one you read from?
In such case, the best may be to have all the updating login in a stored
procedure, unless the logic is too complex for SQL.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||We are seeing that EXACT same error popup randomly on our web server
running Windows 2003 Server. Here's our setup:

Web Server 1 - Windows 2000 Server - Latest SP, MDAC, All Updates, etc.
Web Server 2 - Windows 2003 Server - All updates
SQL Server - Windows 2003 Server, SQL Server 2003 SP3a, All Updates

Now, we used to have both our web servers be 2000 server. We've
recently updated the 2nd one to be 2003 server. Ever since we have
been getting that same error (below) at random times. We seem to think
that it may fail under heavy load.

"Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. "

We've done just about everything we've seen suggested here in the
groups and on Microsoft's site without any success. We can't nail down
what is causing this problem. If ANYBODY can help point us in the
right direction we'd greatly apprecaite it. What's odd is that I've
seen many posts reporting this same problem without any solutions
given. It seems that maybe there's some kind of issue with 2003 that
hasn't been documented or exposed yet.|||This would be my checklist.. (1 and 2 are probably ruled out given that the
2000 server works with no problem)

one clue you may have to help you is the amount of time the web server takes
to throw that error when you access the page. Is it trying to connect and
then giving up after a few seconds, or is it failing immediately without
attempting to handshake with the server.

1) Check the DB server loading, increase connection timeout if its heavy for
extended periods.
2) continuously ping the DB server from the web server(s) to see if there
are connection outages/packet loss.
3) Try a different(older) version of MDAC on the web server, fiddle with the
client settings.
4) Try varying the connection type (SQL logon Windows logon Named Pipes
TCP/IP)
5) Run IIS under a different isolation level, fiddle with the application
settings
7) trap the error in the connection code and attempt a re-connect (3 strikes
and your out).
6) try something else ...
8) get the 2000 server back out

hope you find a solution to the problem, let us know if you do. :)

Mr Tea
http://mr-tea.blogspot.com

"Don Kitchen" <donkitchen@.gmail.com> wrote in message
news:1106327170.237818.86870@.c13g2000cwb.googlegro ups.com...
> We are seeing that EXACT same error popup randomly on our web server
> running Windows 2003 Server. Here's our setup:
> Web Server 1 - Windows 2000 Server - Latest SP, MDAC, All Updates, etc.
> Web Server 2 - Windows 2003 Server - All updates
> SQL Server - Windows 2003 Server, SQL Server 2003 SP3a, All Updates
> Now, we used to have both our web servers be 2000 server. We've
> recently updated the 2nd one to be 2003 server. Ever since we have
> been getting that same error (below) at random times. We seem to think
> that it may fail under heavy load.
> "Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> access denied. "
> We've done just about everything we've seen suggested here in the
> groups and on Microsoft's site without any success. We can't nail down
> what is causing this problem. If ANYBODY can help point us in the
> right direction we'd greatly apprecaite it. What's odd is that I've
> seen many posts reporting this same problem without any solutions
> given. It seems that maybe there's some kind of issue with 2003 that
> hasn't been documented or exposed yet.|||"Don Kitchen" <donkitchen@.gmail.com> wrote in message
news:1106327170.237818.86870@.c13g2000cwb.googlegro ups.com...
> We are seeing that EXACT same error popup randomly on our web server
> running Windows 2003 Server. Here's our setup:
> Web Server 1 - Windows 2000 Server - Latest SP, MDAC, All Updates,
etc.
> Web Server 2 - Windows 2003 Server - All updates
> SQL Server - Windows 2003 Server, SQL Server 2003 SP3a, All Updates
> Now, we used to have both our web servers be 2000 server. We've
> recently updated the 2nd one to be 2003 server. Ever since we have
> been getting that same error (below) at random times. We seem to
think
> that it may fail under heavy load.
> "Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> access denied. "
> We've done just about everything we've seen suggested here in the
> groups and on Microsoft's site without any success. We can't nail
down
> what is causing this problem. If ANYBODY can help point us in the
> right direction we'd greatly apprecaite it. What's odd is that I've
> seen many posts reporting this same problem without any solutions
> given. It seems that maybe there's some kind of issue with 2003
that
> hasn't been documented or exposed yet.

I've seen the same problem and I think it's specifically a memory
problem. It may be a more general "resource" issue correlated with
memory but it is hard to pin down.|||I'm happy to report that we have found the fix to our problems.
Without going into too much detail here, I can say that 2 registry keys
that we created in Windows 2003 fixed the problem.

See these two threads for more info about it:

http://groups-beta.google.com/group...57109e74649c7c8

http://groups-beta.google.com/group...075084961f5cdb7

Loosing SQL connecting while running ASP

We are running a fairly simple ASP application, that connect to a MS SQL ser
ver.
It selects from one table (15.000 records), and updates into another table.
But when we execute the ASP script, it reports the following error, after co
mpleting between 70%-80%. This varies each time it runs
“Microsoft OLE DB Provider for SQL Server error '80004005' [DBNETLIB][ConnectionOpen
(Connect()).]SQL Server does not exist or access denied. “
The application is running in ths following environment
Server 1
Fujitsu-Siemens RX300 Server(Dual 2,8 Xeon, 1 Gb Ram, 72 Gb SCSI Raid 1)
Windows 2003 Web Server
Server 2
Fujitsu-Siemens RX300 Server(Dual 2,8 Xeon, 1 Gb Ram, 72 Gb SCSI Raid 1)
Windows 2003 standart server
Microsoft SQL server 2000I would recommend getting network traces on both the IIS machine and the
SQL Server while the ASP page is being called. The error you posted is
related to a new connection, not an existing query. Increasing the
Logintimeout in code may help, but it sounds like either a coding issue or
Name Resolution problem.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Same problem after installing IIS6.0,
after a lot of tries, I have switched DB connection to ODBC,
I have the problem with OLEDB, I did not face the problem with ODBC.|||

quote:

---

Originally posted by mrgray
Same problem after installing IIS6.0,
after a lot of tries, I have switched DB connection to ODBC,
I have the problem with OLEDB, I did not face the problem with ODBC.

---

hi guys , any leads with the problem ? because i am stuck with this issue to
o hosting my script and MS SQL on seperate server
i am also running win2k3 and IIS6
DEAD END

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 result printed

hi,
i am running the following script. My question is that I see the results in
diff query panels... How can I put result set together and so I could copy
and paste to notepad? thanks
DECLARE cur_tables CURSOR FOR
SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
BEGIN
SET @.SQL = ''
BEGIN
SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
select @.returns = EXEC ( @.SQL)
END
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
END --WHILE
CLOSE cur_tables
DEALLOCATE cur_tables
GOIf running it manually in either QA, or MS there is an option on the Query
menu to send 'Results to text', or something like that.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23I2NuwiJIHA.2712@.TK2MSFTNGP06.phx.gbl...
> hi,
> i am running the following script. My question is that I see the results
> in diff query panels... How can I put result set together and so I could
> copy and paste to notepad? thanks
> DECLARE cur_tables CURSOR FOR
> SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
> OPEN cur_tables
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> BEGIN
> SET @.SQL = ''
> BEGIN
> SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
> select @.returns = EXEC ( @.SQL)
> END
>
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> END --WHILE
> CLOSE cur_tables
> DEALLOCATE cur_tables
> GO
>

looping result printed

hi,
i am running the following script. My question is that I see the results in
diff query panels... How can I put result set together and so I could copy
and paste to notepad? thanks
DECLARE cur_tables CURSOR FOR
SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
BEGIN
SET @.SQL = ''
BEGIN
SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
select @.returns = EXEC ( @.SQL)
END
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
END --WHILE
CLOSE cur_tables
DEALLOCATE cur_tables
GO
If running it manually in either QA, or MS there is an option on the Query
menu to send 'Results to text', or something like that.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23I2NuwiJIHA.2712@.TK2MSFTNGP06.phx.gbl...
> hi,
> i am running the following script. My question is that I see the results
> in diff query panels... How can I put result set together and so I could
> copy and paste to notepad? thanks
> DECLARE cur_tables CURSOR FOR
> SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
> OPEN cur_tables
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> BEGIN
> SET @.SQL = ''
> BEGIN
> SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
> select @.returns = EXEC ( @.SQL)
> END
>
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> END --WHILE
> CLOSE cur_tables
> DEALLOCATE cur_tables
> GO
>
sql

looping result printed

hi,
i am running the following script. My question is that I see the results in
diff query panels... How can I put result set together and so I could copy
and paste to notepad? thanks
DECLARE cur_tables CURSOR FOR
SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
BEGIN
SET @.SQL = ''
BEGIN
SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
select @.returns = EXEC ( @.SQL)
END
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
END --WHILE
CLOSE cur_tables
DEALLOCATE cur_tables
GOIf running it manually in either QA, or MS there is an option on the Query
menu to send 'Results to text', or something like that.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23I2NuwiJIHA.2712@.TK2MSFTNGP06.phx.gbl...
> hi,
> i am running the following script. My question is that I see the results
> in diff query panels... How can I put result set together and so I could
> copy and paste to notepad? thanks
> DECLARE cur_tables CURSOR FOR
> SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
> OPEN cur_tables
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> BEGIN
> SET @.SQL = ''
> BEGIN
> SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
> select @.returns = EXEC ( @.SQL)
> END
>
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> END --WHILE
> CLOSE cur_tables
> DEALLOCATE cur_tables
> GO
>

Loopback adapter

I am running Windows XP and have SQL Server 2005 Enterprise Edition that I use for testing on my notebook. I want to install Oralce 11g on it as well and use VS 2005 as a front end programming. For me to install Oracle I need to install a loopback adapter. If I do this will it mess up SQL Server 2005 or will it not affect SQL Server 2005? Please advise

Hi,

Do you mean you want to install Microsoft Loopback Adapter on your Windows XP machine?

The Microsoft Loopback adapter is a testing tool for a virtual network environment where network access is not available. It will not affect the Microsoft SQLServer2005.

For more information on installation, see:
http://support.microsoft.com/kb/839013/en-us

Thanks.

Loop

Hi ,
I like to build a query to generate a virtual running number from the
table.
Something like "Select @.X , fieldA from TableA" . The result should look
like ,
@.x FieldA
-- --
1 AAA
2 BBB
3 CCC
Please help
Travis Tan
On Wed, 5 Oct 2005 00:04:04 -0700, Travis wrote:

>Hi ,
> I like to build a query to generate a virtual running number from the
>table.
>Something like "Select @.X , fieldA from TableA" . The result should look
>like ,
>@.x FieldA
>-- --
>1 AAA
>2 BBB
>3 CCC
>Please help
Hi Travis,
Maybe something like this?
SELECT COUNT(*) AS [@.x], a.FieldA
FROM TableA AS a
INNER JOIN TableA AS b
ON b.FieldA <= a.FieldA
GROUP BY a.FieldA
(untested - see www.aspfaq.com/5006 if you prefer a tested solution)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

lookup task running very slow when deal w/big tables

I try to convert a Procedure that join 8 tables with INNER AND OUTER JOIN, my understanding is that the Lookup task is the one to use and I should break these joins into smaller block, it takes a long time to load when I do this, since each of these tables had 10-40mill. rows and I have 8 tables to go thru, currently this Stored Procedure took 3-4min to run, by converting this to 8 Lookup tasks, it ran for 20min. has anyone run into this issue before and know a work around for this.

Thanks

Are you aware of the different caching types of the Lookup Tranformation? by default the lookup caches the whole data set from your lookup table/query; if you are not using a query to limit the number of columns/rows to only the ones the lookup needs, so the cached data is limited to what you really need, this would be a good point to start. Try, if possible, to re-arrange the tasks in your data flow so your lookup transforms are based on the tables with the lesser rows.

You can see more information about lookup tranformation here: http://msdn2.microsoft.com/en-us/library/ms141821.aspx

Lookup transforms may not be the best approach for all cases; if the performace you are getting for having your dataset out of a single query/SP is better than the lookup tasks approcah why would you want to change it?

Thanks

Rafael Salas

|||

Yes, I am setting 500mb Cache size and select 3 integer and 2 varchar(50) fields only, I also try to re-arrange the lookup tables, but since all of them are big, it's limited what I can do by re-arranging them. The reason that I am doing this is to evaluate if it pay to convert our current procedure or not, the main thing that I try to test is performance, it's only make sense if SSIS tasks can out performance stored procedure during our data pull. I wish that SSIS allows input parameters mapping in the reference table TAB. this will allows me to limit number of lookup rows that get load into SSIS. also I found that the OLE DB Source task is very picky w/what kind of SQL script it can run, if you have a sub query that contains parameter, it won't take it, even it's perfectly ok to run in TSQL, this is so bad since sometime you get better performance by putting parms into sub query to limit number of rows before you join to another huge table.

Anyway I think I found a work around, I am currently switched over to use Merge Join task, and it seems to work better. But it still had not out performance current stored procedure. I just hope that some one out there w/similar issue, so I can compare note.

Thanks

|||

by using Merge Join trasnformation , I think you may actually be walking away from any performace gain. By definition Merge Join is an asynchonous trasnformation, so it would use more resources(memory) than the lookup transformation and more likely to have poorer performance; it actually requieres the inputs to be sorted . I think there are good reasons for not having a parameter mapping ability in the reference table tab of the lookup and I just can see that it would requiere to execute a query for every row comming to the lookup input. Actually if you want to test it, you can go to advanced tab and edit the query to include and map your parameters; the performance will hit the floor.

BTW, 500Mb in cache size may no be enough for the volume of data in your lookups.

You can review an interesting paper about SSIS performance at:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

Sorry if my post were not helpfull at all

Rafael Salas

|||

Thanks for you responses, it does help. I am just a big disappointed, since all the seminars that I attended seems to advise people to convert their SQL script into SSIS tasks for better maintenance and performance, but I have yet to find a work around that can outperform current stored procedure, since our source schema is fairly complex and required a lot of joins in order to pull data out, I guess you were right that SSIS tasks may not be the best choice for every situation in term of performance. Also, you are right about the Merge Join task, it actually performs 2-3 times worse than current stored procedure, and I guess I will stay w /my stored procedures for now.

Thanks a lot your help

Lookup Task Datatype issue

Hello:

I'm attempting to use the lookup task and i'm running into an issue about incompatible datatypes.

The table that i'm referencing in the lookup has a field named DateCreated, and it's DataType is DateTime.

But when I hook it up to the Lookup Task, the lookup task thinks it's a DB_Timestamp, so i get an incompatible data type error.

What can i do?

i'm not sure if this would resolve your issue, but have you tried using the data conversion component?|||

Duane:

Thanks for your reply.

My problem is not with the dataflow, but with the Lookup Task misinterpreting the Data Type of one of the columns in my lookup table.

Instead of being DateTime as the column is set to, the the Lookup task thinks the column is a DB_Timestamp.

|||

SamuelEe,

maybe i misunderstood you. however, the lookup transformation is a data flow component.

|||

SamuelEe wrote:

Duane:

Thanks for your reply.

My problem is not with the dataflow, but with the Lookup Task misinterpreting the Data Type of one of the columns in my lookup table.

Instead of being DateTime as the column is set to, the the Lookup task thinks the column is a DB_Timestamp.

Duane's suggestion is still valid. Try changing the type of the column in the pipeline to DT_DBTIMESTAMP as well so that it can match with what the LOOKUP component has got.

The LOOKUP component is not misinterpreting anything. Datetime fields from database tables get interpreted as DT_DBTIMESTAMP in a SSIS data flow.

-Jamie

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 table / view that will tell me if I need to reinitialize subscription

I have kind of unique situation. I am running Merge replication. In one of my publications I am only publishing procedures/functions/views. By design, these do not change that often, but when a programmability object changes, it is scripted in a way so that:

1. The article is dropped from the publication

2. the object is then changed

3. The article is added back to the publication

My question is: Is there a table or view that the subscriber or publisher can see that could tell me if reinitialization needs to occur. I am looking at adding an automated script at the subscriber that makes the determiniation and automatically reinitializes the subscription. My alternative is to force the subscriber to reinitialize every time when synchronizing with this publication, even if nothing has changed because the process has to be automated.

Thanks,

Bill

Are you using SQL 2000 and 2005? In SQL 2005, you don't need to drop/recreate articles in order to change the schema. You can directly do ALTER TABLE/VIEW/FUNCTION. For more info, please take a look at BOL http://msdn2.microsoft.com/en-us/library/ms151870.aspx.

Peng

|||I am using 2005, but that will not work, as the publication only contains programmability objects, the underlying base tables are in another publication.|||Not sure what you mean by that will not work. What Peng means is that when you need to change one your progammability article like stored proc or view etc, you can just run alter view or alter proc ... and this DDL action will be replicated when using SQL 2005. so you dont need to drop the article, alter it and readd it. Hence you will not even need to reinitialize your subscriptions.|||When you execute a DDL statement against a view that: is in a publication without any tables in that publication, the query will run but will not complete execution.|||This is a known issue and the workaround is to add a dummy table in that publication.|||When you say create a dummy table, what exactly do you mean? Is this just the "real" table name with one column or something else?|||

Correct, create a non-necessary table that means absolutely nothing. i.e.

create table dbo.t1 (col1 int primary key, col2 int)

Add this as an article to your publication, then your DDL statements should work.

Looking for some SQL Advice

I'm working on a project to create a Report. Currently, I have an
Excel spreadsheet with a macro running that creates the report that I
want to move to SQL reporting.
There are 2 tables. One Table has 2 sets of price ranges, with a
"StartPrice", "EndPrice", and a "Type" to select between the 2 set of
ranges.
One of the ranges is like:
Type1 $0.00 $49.00
Type1 $49.01 $79.00
Type1 $79.01 $99.00
Type1 $99.01 $129.00
Type1 $129.01 $149.00
Type1 $149.01 $179.00
Type1 $179.01 $199.00
Type1 $199.01 $249.00
Type1 $249.01 $299.00
Type1 $299.01 $349.00
Type1 $349.01 $399.00
Type1 $399.01 $499.00
Type1 $499.01 $599.00
Type1 $599.01 $699.00
The other table has sales data with a qty, sellingprice, Actualcost
that I calculate the Total Qty, Total Cost, Total SellingPrice, and
Profit, for each of the price ranges.
On the report its like:
Range Qty Price Cost Profit
$299.01 - $349.00 1 $349.00 $142.28 $206.72
$349.01 - $399.00 1 $362.38 $180.93 $181.45
$399.01 - $499.00
To achieve this output, I loop thru each of the Price ranges, pluging
the Start and End into a Select to select the single record for the
Report.
I was wondering if there was a way to simply by using only one SQL
command.
Any ideas?
BartYes, you can join the sales data to the range data where the Total Cost is between the Start Price and the End Price.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||Could you show me an example?
Using my rough field names would be fine.
Bart
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:O5icBq7YEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Yes, you can join the sales data to the range data where the Total Cost is
between the Start Price and the End Price.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.