Do I need to reinstall or patch something?

Hi,

I have discovered something wrong with my SSIS package and I thought it best to post a new thread since the problem is not what I thought it was initially.

With relation to my post of 'directory variable problem' I have now discovered by opening my project this morning that apparently my project had gone corrupt, according to the dev environment since the message reads:

"There were errors while the package was being loaded.
The package might be corrupted.
See the Error List for details."

The Error List:
"Error 13 Error loading MyTestSSISPackage.dtsx: The connection "(local).MyTestDB.sa" is not found. This error is thrown by Connections collection when the specific connection element is not found."

This is a buggy ADO.net connection that I deleted yesterday since, even though it connects successfully, doesn't want to work! It keeps on telling me log in failed for user <MyTestUser> even after I test it and it connects fine with the username and password during the UDL screen of the connection.. It's like it forgets the bloody password.
I created another connection, this time an OLE connection and it seems to work fine.

*EDITED*
I stand corrected..no bloody connection manager is working now. They all fail stating that they cannot connect to the db due to some or another 'unsupported' value in the connection string...the connectionstring was set up by the environment's udl? how is this possible. I am beginning to get annoyed with SSIS

Now finally my question...WHY is it that every time I change something its like the dev environment doesn't cascade the change throughout the project or package? The problem I have been having with my variables not getting picked up with the correct values which I gave them during design time is probably also related to this. How or where can I remove the reference to this old ADO.Net connection since I cannot find it anywhere in the <package>.dtsx file?

Anyone ever experienced this?

Regards
Mike

I have found that I have to manually replace the ID of the connection managers with the newly created one directly in the dtsx file since it says that my package is corrupted after deleting a connection manager. This is a very annoying feature or lack of it.

However, I still can't get past the: "Login failed for 'sa'" ERROR with the following description:

at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at ScriptComponent_6d604c38c05848379605ada1e61bdda6.ScriptMain.Input_ProcessInputRow(InputBuffer Row)
at ScriptComponent_6d604c38c05848379605ada1e61bdda6.UserComponent.Input_ProcessInput(InputBuffer Buffer)
at ScriptComponent_6d604c38c05848379605ada1e61bdda6.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
|||Does anyone know where I can find the pakage.connections?

I think i can remove some of my headaches by cleaning that out a little

divide by zero error

Hello,
When I originally wrote the SQL below, I didn't think the last two fields
could ever be zero, but I was wrong. How can I rewrite this so if
NUM_PRODUCTION or NUM_PIECE_SIZE are zero, TotWithFuel simply equals zero?
SELECT
(dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE +
(dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_FUEL_RATE -
dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_BASE_FUEL_RATE)
* dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL)
/ dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION /
dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE AS TotWithFuel
Thanks in advance!
AmberTry:
SELECT
isnull(
(dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE +
(dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_FUEL_RATE -
dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_BASE_FUEL_RATE)
* dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL)
/
nullif(dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION, 0) /
nullif(dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE, 0)
, 0) AS TotWithFuel
AMB
"amber" wrote:

> Hello,
> When I originally wrote the SQL below, I didn't think the last two fields
> could ever be zero, but I was wrong. How can I rewrite this so if
> NUM_PRODUCTION or NUM_PIECE_SIZE are zero, TotWithFuel simply equals zero?
> SELECT
> (dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE +
> (dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_FUEL_RATE -
> dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_BASE_FUEL_RATE)
> * dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL)
> / dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION /
> dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE AS TotWithFuel
> Thanks in advance!
> Amber|||"amber" <amber@.discussions.microsoft.com> wrote in message
news:50239A89-6D09-4DE2-805F-3A6E55FBEA4B@.microsoft.com...
> Hello,
> When I originally wrote the SQL below, I didn't think the last two fields
> could ever be zero, but I was wrong. How can I rewrite this so if
> NUM_PRODUCTION or NUM_PIECE_SIZE are zero, TotWithFuel simply equals zero?
> SELECT
> (dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE +
> (dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_FUEL_RATE -
> dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_BASE_FUEL_RATE)
> * dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL)
> / dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION /
> dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE AS TotWithFuel
> Thanks in advance!
> Amber
SELECT
CASE WHEN dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL = 0 OR
dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE = 0 THEN 0 ELSE
(dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE +
(dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_FUEL_RATE -
dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_BASE_FUEL_RATE)
* dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL)
/ dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION /
dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE END AS TotWithFuel|||SELECT case when dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION = 0 or
dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE
= 0 then 0
else (dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE +
(dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_FUEL_RATE -
dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_BASE_FUEL_RATE)
* dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL)
/
dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION /
dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE
end AS TotWithFuel
I would also consider using an alias on your tables to make it easier to
work with, at least to get rid of the dbo. in the column aliases. That
looks hard to read :)
just add AS <alias> like:
from dbo.tablename as tablename or something like this, but even shorter.
Don't lose the meaning of the name in the shortend version, thought. What
you have is better than using the kind of aliases some folks use: (A.? B.?
C.?) When you have like 10-15 tables in the query who knows what is going
on.
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"amber" <amber@.discussions.microsoft.com> wrote in message
news:50239A89-6D09-4DE2-805F-3A6E55FBEA4B@.microsoft.com...
> Hello,
> When I originally wrote the SQL below, I didn't think the last two fields
> could ever be zero, but I was wrong. How can I rewrite this so if
> NUM_PRODUCTION or NUM_PIECE_SIZE are zero, TotWithFuel simply equals zero?
> SELECT
> (dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE +
> (dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_FUEL_RATE -
> dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_BASE_FUEL_RATE)
> * dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL)
> / dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION /
> dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE AS TotWithFuel
> Thanks in advance!
> Amber

distribution database having problems??

Something is wrong with my distribtion database. When the distribution clean
up agent runs (or I run dbo.sp_MSdistribution_cleanup manually)
I get the following error message:
Executed as user: sa. Table error: Database ID 9, object ID 5575058, index
ID 1. Chain linkage mismatch. (1:89299)->next = (1:59339), but
(1:59339)->prev = (0:0). [SQLSTATE HY000] (Error 8908). The step failed.
SO then I run
DBCC CHECKDB
on the distribution database. The MSrepl_commands is the table with the
problem with repeated messages such as:
Table error: Object ID 5575058, index ID 1. B-tree page (1:91019) has two
parent nodes (1:93624), slot 103 and (1:89299), slot 1.
I run this:
dbcc checktable ('MSrepl_commands')
and get the same type of error message.
I have no idea how to fix this. Any advise?? (If I try and use the repair
options of the dbcc commands than it says I have to have the DB in single
user mode. Not sure if you are even suppose to do that with a distribution
db?)
Thanks so much for any and all help,
Kristy
Believe it or not these problems crop up from time to time on replicated
databases. Stop the sql server agent on the publisher, yank the network
cable (if possible), and put this database in single user mode and do the
repair.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:OCFMw7kPGHA.1696@.TK2MSFTNGP14.phx.gbl...
> Something is wrong with my distribtion database. When the distribution
> clean
> up agent runs (or I run dbo.sp_MSdistribution_cleanup manually)
> I get the following error message:
> Executed as user: sa. Table error: Database ID 9, object ID 5575058, index
> ID 1. Chain linkage mismatch. (1:89299)->next = (1:59339), but
> (1:59339)->prev = (0:0). [SQLSTATE HY000] (Error 8908). The step failed.
> SO then I run
> DBCC CHECKDB
> on the distribution database. The MSrepl_commands is the table with the
> problem with repeated messages such as:
> Table error: Object ID 5575058, index ID 1. B-tree page (1:91019) has two
> parent nodes (1:93624), slot 103 and (1:89299), slot 1.
> I run this:
> dbcc checktable ('MSrepl_commands')
> and get the same type of error message.
> I have no idea how to fix this. Any advise?? (If I try and use the
> repair
> options of the dbcc commands than it says I have to have the DB in single
> user mode. Not sure if you are even suppose to do that with a distribution
> db?)
> Thanks so much for any and all help,
> Kristy
>
>
|||Thanks Hilary. I will check to see if we can yank the network cable. Not
sure if we can though because it is on a remote server hosted by another
company in another state.
If not, is there something else I can do? (Besides redo replication and drop
the distribution DB.)
As always, thanks!
--Kristy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eODseSmPGHA.5516@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Believe it or not these problems crop up from time to time on replicated
> databases. Stop the sql server agent on the publisher, yank the network
> cable (if possible), and put this database in single user mode and do the
> repair.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:OCFMw7kPGHA.1696@.TK2MSFTNGP14.phx.gbl...
index[vbcol=seagreen]
failed.[vbcol=seagreen]
two[vbcol=seagreen]
single[vbcol=seagreen]
distribution
>

Distribution Cost

Sorry if this is the wrong place to post this.....

I am new to MS SQL as I have been using Paradox for years. Paradox has its own built in DB, so, the cost is great for small company applications (which is what we do). However, we are now going to rewrite our application in VB (most likely), and we want to ensure that the DB we go with will not cost a fortune to our clients. On top of this we want the application to be PC based as well as online. I know you can connect to MS SQL via PHP. The DB will live on our server, and the clients should all be hosted on our server. Our clients are Churches, and they do not have thousands to put into a program, which is why Paradox was great for this. Now, I am worried that MS SQL will cost for every copy of the program we sale. How does this work if we develop a program to work with MS SQL 2005 and sale it to clients? Is there a cost that we must pay MS for each copy? Does the client need the MS SQL Desktop Engine to run the DB, or do they need something else?

Sorry to sound stupid, but MS SQL is new to me, and I want to ensure that I make the right choice that is not going to bite me later down the road. I have looked at MySQL, PostgreSQL, OpenBase, and a few others. The all seem ok, but still looking. Thanks in advance for any suggestions or comments you have...

Gene

Hi Gene,

Perhaps SQL Server Express edition will meet your business needs. Check out http://www.microsoft.com/sql/editions/express/default.mspx for details. You may also find this topic useful for comparing the features available in each edition: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx and this site contains useful information about pricing and licensing http://www.microsoft.com/sql/howtobuy/default.mspx.

Regards,

Gail

Distribution agent fails with the following error message

The concurrent snapshot for publication<PubName> has not been activated by
the Log Reader Agent.
What did I do wrong ? I reinitinalized a subscription and the snapshot has
completed, but the distrib agent fails to start ..
has the snapshot agent started yet? Is the log reader agent started? Can you
bounce it?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jim" <jim@.hotmail.com> wrote in message
news:%23USywHPZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> The concurrent snapshot for publication<PubName> has not been activated by
> the Log Reader Agent.
> What did I do wrong ? I reinitinalized a subscription and the snapshot has
> completed, but the distrib agent fails to start ..
>
|||I tried all of those..
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OTgqX%23PZFHA.892@.TK2MSFTNGP10.phx.gbl...
> has the snapshot agent started yet? Is the log reader agent started? Can
you[vbcol=seagreen]
> bounce it?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Jim" <jim@.hotmail.com> wrote in message
> news:%23USywHPZFHA.3648@.TK2MSFTNGP14.phx.gbl...
by[vbcol=seagreen]
has
>
|||I'd drop the subscription and try again.
One more thing, if you deployed this publication from script make sure you
set @.status='active' in your sp_addpublication call,
ie
sp_addpublication 'test',@.status='active'
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jim" <jim@.hotmail.com> wrote in message
news:uLuYLySZFHA.228@.TK2MSFTNGP12.phx.gbl...
>I tried all of those..
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OTgqX%23PZFHA.892@.TK2MSFTNGP10.phx.gbl...
> you
> by
> has
>