SSIS Connection Problem in C# - Database Discussion Boards

MWRivera wrote:
setting the actual column in the database up as a password column in some way

I'm not sure what you mean by this. Maybe this article will be of help:

http://ianywheresolutions.net/developer/product_manuals/sqlanywhere/1000/en/html/dbdaen10/da-security-s-4649816.html[^]

This obviously leaves you with the problem of how to secure the key used to encrypt and decrypt the password; I for one don't much like the idea of hard-coding it into the trigger like this. But it's a start.

This is for SQLAnywhere, which I think you said you were using. If it is Sybase ASE there is a more sophisticated method for doing this sort of thing which is explained in the documentation.

Thanks for the link David, I'll check it out at the next opertunity I get.

Mel

Hi David,

Thanks for the link above. I was wondering if you or anyone else could help with a problem I've came into with it though.

I was able to encrypt the password field using the following trigger when a new record was added to the table:

ALTER TRIGGER "encrypt_new_user_pwd" BEFORE INSERT
ORDER 1 ON "QAS"."tableName"
REFERENCING NEW AS newPwd
FOR EACH ROW
BEGIN
Set newPwd.pwdField = ENCRYPT(newPwd.pwdField , 'key')
END

The problem is I'm unable to decrypt the password of the newly created record (fieldID = 0002), using the following:

SELECT CAST (DECRYPT(pwdField, 'key') AS VARCHAR(100))
FROM "QAS"."tableName" WHERE fieldID = '0002'

In Sybase when trying to execute the above statement I get the following error:

Interactive SQL
The following error occurred while fetching results:
Decryption error: Input must be a multiple of 16 bytes in length for AES
SQLCODE=-851, ODBC 3 State="08001"

Do you have any idea what is going wrong here?

The pwdField is of type VARCHAR and size 15.

Thanks,
Mel

Hi all
how can i get Primary key column name from table?I find some tips from google but it's not working always show no row selected.

select all_constraints.index_name, all_ind_columns.column_name, all_ind_columns.column_position from all_constraints, all_ind_columns where all_constraints.owner = all_ind_columns.index_owner and all_constraints.constraint_name = all_ind_columns.index_name and all_constraints.constraint_type = 'P' and all_constraints.owner = 'DBO' and all_constraints.table_name = UPPER('VARTABLE') order by all_constraints.index_name asc, all_ind_columns.column_position asc, all_ind_columns.column_name asc

please help me.

try hitting the enter key sometime!

Never underestimate the power of human stupidity
RAH

ok next time.

Try this:

http://www.techonthenet.com/oracle/questions/find_pkeys.php[^]

Goood Day All

I have a SSIS package that i have created and i want to execute in C# in my web Application. and i have a Connection manager named "OMEGA" and in my C# am trying to execute it like this

//Start the SSIS Here
Application app = new Application();
Package package = null;
package = app.LoadPackage(@"C:\O!WEB_SETUP_VERSIONS\SSIS_Compiled\OMEGA.dtsx", null);
Variables vars = package.Variables;
vars["Time1"].Value = time;
vars["Time2"].Value = time;
vars["Time3"].Value = time;
vars["TTBLTYPE"].Value = THREAD_DATA[1].ToString();

ConnectionManager Omega = package.Connections.Add("OLEDB");
Omega.Name = "OLEDBConnection";
String Strcon = obj.GetConnectionString(THREAD_DATA[0].ToString());
Omega.ConnectionString = Strcon;

//package.Connections["SQLNCLI10.1"].ConnectionString = obj.GetConnectionString(THREAD_DATA[0].ToString());
//Excute Package
Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
String Log;

if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
{
foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors)
{
// Log = local_DtsError.Description.ToString();

//CommonFunctions.writeLogFile(Log,true);

}
}

}

After it executed the package, when i loop through the exceptions i get this

SSIS Error Code DTS_E_OLEDBERROR.   An OLE DB error has occurred. Error code: 0x80004005.\r\nAn OLE DB record is available.   Source: \"Microsoft SQL Server Native Client 10.0\"   Hresult: 0x80004005   Description: \"Login timeout expired\".\r\nAn OLE DB record is available.   Source: \"Microsoft SQL Server Native Client 10.0\"   Hresult: 0x80004005  Description: \"A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.\".\r\nAn OLE DB record is available.   Source: \"Microsoft SQL Server Native Client 10.0\"   Hresult: 0x80004005   Description: \"TCP Provider: No connection could be made because the target machine actively refused it.\r\n\".\r\n

Please note that i want to use my webconfig not SSIS Config. in the Function GetConnectionString i return a Connection string and Pass it to the package.

Thanks

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

1: Is the connection string correct?
2: Do you have the requisite permissions?
3: Check the SQL configuration via the Sql Server Configuration Manager (was called somehting else in 2005: maybe Surface Area something or other) and ensure that it is set up correctly for your installation.
4: AS I recall the error you have listed is quite common: a quick search on Google would yield plenty of results. Here is one I found in about a second: TCP Provider: No connection could be made because the target machine actively refused it [^].

Tychotics: take us back to the moon

"Life, for ever dying to be born afresh, for ever young and eager, will presently stand upon this earth as upon a footstool, and stretch out its realm amidst the stars."
H. G. Wells

1: The connection string is Correct it comes from my webconfig

2: Can you give me more info on this one. Thanks

3: what Configuration needs to be there ? i have SQl 2005 installed and 2008 installed they both use the same port

4: i saw that links and i have Googled

Thanks

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

hi,
currently i m using sql server 2000.
i declared one column as "startdate"
and the value as follows...

startdate="23-Feb-2010 4:00PM"
startdate="24-Feb-2010 4:00PM"

when i query as below i m not getting any record.

select * from tablename where startdate between '23-Feb-2010' and '23-Feb-2010'

can anyone help me? - KARAN

There aren't any dates between 23 feb 2010 and 23 feb 2010. Thats why youre query doesn't return anything. Try:

select * from tablename where startdate between '23-Feb-2010' and '24-Feb-2010'

Wout Louwers

The idea is good, the suggestion isn't. There still isn't any date between those when treated as exclusive.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.

His startdates where:

startdate="23-Feb-2010 4:00PM"
startdate="24-Feb-2010 4:00PM"

So the first one is between the 23th and the 24th, isn't it?

Wout Louwers

sorry, you're right. I would have given an example with a broader range though.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.

Read the documentation for your database.

http://www.w3schools.com/sql/sql_between.asp[^] says:

"In some databases, persons with the LastName of "Hansen" or "Pettersen" will not be listed, because the BETWEEN operator only selects fields that are between and excluding the test values).

In other databases, persons with the LastName of "Hansen" or "Pettersen" will be listed, because the BETWEEN operator selects fields that are between and including the test values)."

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.

i have gave my start date's. while i remove the timing, its work fine. but when the timing is included it fails. so when i m storing datetime, i always want to do in sepearate way only?

for example want to create columns as... Startdate-StartTime, EndDate-EndTime,MaxDate-MaxTime....??

help me - Karan

. No

store date/time information as real DateTime, not as string, not as separate date and time.

But be aware that "between" may or may not exclude the values you specify, i.e. depending on the database "BETWEEN a and b" means "a <= x <= b" or "a < x < b"

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.

On Oracle you could try

select * from tablename where trunc(startdate) between '23-Feb-2010' and '23-Feb-2010'

There is likely an equivalent for SQL Server.

[EDIT]
I'd also add that you are asking for a compare between two different database types so an implied conversion will take place. That conversion often causes results to be different than expected. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

Try this:

select * from tablename where (datediff(day, startdate, '23-Feb-2010') = 0)

Adam

Hi All,

I'm working with SQL Anywhere 10 and one of the tables in our database has a password field we are wanting to encrypt in some way.

Is there a quick and easy way to do this? It's just so if anyone looks at the data in the database they can't see users passwords.

Thanks,
Mel

Hash the values before you insert them into the database.

I know the language. I've read a book. - _Madmatt

Hi Mark,

Thanks for your feedback, do you have an example of that?

Is there not a constraint in sybase or something along those lines that I can apply to the column?

Thanks again,
Mel

I have two table which are TBL_A and TBL_B.

TBL_A contain two record, TBL_B have no record.

Select ta.Name
From TBL_A ta

I get two record. But If wrote following query

Select ta.Name
From TBL_A ta, TBL_B tb

No any record get, because SQL Server defaultly use CROSS JOIN like this;

Select ta.Name
From TBL_A AS ta CROSS JOIN TBL_B AS tb

How can I close CROSS JOIN. I want to get record when TBL_B also have no record..

Actually I use "where" statement, but if one table have no record on FROM statement so I did not get result related about CROSS JOIN.

If I understand your question correctly, what you are looking for is an OUTER JOIN:

http://msdn.microsoft.com/en-us/library/ms187518.aspx[^]