Re: Query / Design Optimization help - Database Discussion Boards

SilimSayo1-Mar-10 16:43

1-Mar-10 16:43

I understand what you are saying but you're not getting my point at all. I am saying that the "compiler" would think that Table1 is a schema because it prefixes the table name employee. In other words, don't write Table1.employee because Table1 would understood differently by the "compiler". If you're worked for 20 years plus with a database, this should be pretty much common sense. I don't know much about how to generate XML but if do a little googling, you should be able to find the answer especially for someone with 20+ years in the IT industry.

Re: xml sql

Mycroft Holmes1-Mar-10 18:10

Mycroft Holmes

1-Mar-10 18:10

SilimSayo wrote:
but you're not getting my point at all

You are quite correct in that, I was only focusing on the question of the OP, not on a general discussion of how the SQL was going to interpret the statement.

Never underestimate the power of human stupidity
RAH

Re: xml sql

Not Active25-Feb-10 14:15

Not Active

25-Feb-10 14:15

SELECT FirstName , LastName 
FROM Employees
FOR XML AUTO, ELEMENTS, ROOT('Table1')

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

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

SQL Server re-installs when already installed

Rich Leyshon25-Feb-10 8:17

Rich Leyshon

25-Feb-10 8:17

I have a project which has been dormant for a year but was just fine when I last used it.

Now, one year on, with nothing changed, when I publish and install, it insists on re-installing SQL Server Express even though it is already installed.

No changes made to the bootstrapper, which used to work just fine ....

And just to add to the fun, the previous Dotfuscator stored settings also fail to obfuscate the code.

I could understand it if I'd been tinkering but this literally hasn't been touched since the last publish a year ago!

Cheers,

Rich

Re: SQL Server re-installs when already installed

Mycroft Holmes25-Feb-10 10:54

Mycroft Holmes

25-Feb-10 10:54

Oooh nasty
So your app has not changed, now it comes down to your environment, you know all those lovely security and product updates courtesy of every vendor with software on your platform, one (or more) of them has probably changed something.

I would think that rebuilding your install may make a difference. You may want to look at changing your references away from 'specific version'

Never underestimate the power of human stupidity
RAH

SQL Server Exported Text File Differences

Member 450523625-Feb-10 3:18

Member 4505236

25-Feb-10 3:18

Morning All,

I have queries that I developed and run in SQL Server that export the results to a Tab delimited text file (no header row). That text file is linked to an Ms Access database using a text file link spec.

The same query run in Server 2005 and Server 2008 results in text files that have the same data but structural differences. I have to have a separate spec for 2005 and 2008.
Also suprising is that the 2008 version of the text file approx half the physical size.

The output of the processing in Access is exactly the same with either version of the text file.

Any ideas in what the difference(s) is between 2005 and 2008? Creating two link specs can be pain if the text file contains dozens of fields.

Thanks for any Help
Gene

Query / Design Optimization help

Laddie24-Feb-10 20:05

Laddie

24-Feb-10 20:05

I have a situation where i need to optimize the query to perform better. possibly a solution.
Here is the situation.

I have a table with the following fields(I am putting only those are significant to the scenirio)

WorkItems[Table]
StartDate
EndDate
Stage
CompletedDate

I need to calculate a value named status according to various conditions involving the above parameters.

Like If StartDate < getdate()
Status = Green
etc..

I created a function called CalculateStatus Passing all the above parameters and added one computed colum in the WorkItems table to call this function.
Since column is non deterministic i cant add a index to this column.

Now when i blindly do a select of status the same is getting calculated very fast

The issue is when i use this computed column with aggragate function like

1) SELECT count(Status) FROM WorkItems

Or when doing a group by

2) SELECT Count(WorkItemId),Status FROM WorkITems
Group BY Status

Could you please guid me how i could optimize the situation so that i can make things faster.

Thanks in advance for your valuable time

Sreehari ARanghat

Thanks
Laddie

Kindly rate if the answer was helpful

Re: Query / Design Optimization help

Chris Meech25-Feb-10 7:00

Chris Meech

25-Feb-10 7:00

Instead of a computed column, use a normal column and use an after update/insert trigger on the table that calls your function and sets the column value for the row in question. You then also have the advantage of adding an index on this column. 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]

Excel to SQL

MAW3024-Feb-10 15:33

MAW30

24-Feb-10 15:33

I received the following error (see line *********error***********) when trying to transfer data from excel to sql using the code below. The sql table is in the database and columns all match the store procedure, does anyone have any idea what creates this problem.

Thanks in advance, Michael

Failed to obtain column collation information for the destination table. If the table is not in the current database the name must be qualified using the database name (e.g. [mydb]..[mytable](e.g. [mydb]..[mytable]); this also applies to temporary-tables (e.g. #mytable would be specified as tempdb..#mytable).

void ExcelToSqlBulkCopy(String strPath, String strFile, String strTableName)
{
strExcelStoredProcedure = "SELECT ID,City,State FROM [Cities$]";
String excelConnectionString = y;
String sqlConnectionString = z;

// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand(strExcelStoredProcedure, connection);
connection.Open();

// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "[MyDatabase].[dbo].[" + strTableName + "]";
bulkCopy.WriteToServer(dr); *********error***********
}
}
connection.Close();
}
}

Re: Excel to SQL

WoutL24-Feb-10 19:54

WoutL

24-Feb-10 19:54

Does the table in strTableName exist in the database MyDatabase? And is dbo the owner of the table?

Wout Louwers

Re: Excel to SQL

hans vogels29-Aug-10 9:21

hans vogels

29-Aug-10 9:21

bulkCopy.DestinationTableName = strTableName
is sufficient, de database is in the sqlConnectionString

Case-Sensative Password Column in SQL Anywhere 10

MWRivera24-Feb-10 5:59

MWRivera

24-Feb-10 5:59

Hi All,

Does anyone know the sql statement/switch or anything of that nature that I can use to create a case-sensative password field in my sybase DB?

Thanks,
Mel

Re: Case-Sensative Password Column in SQL Anywhere 10

David Skelly24-Feb-10 6:23

David Skelly

24-Feb-10 6:23

This might help:

http://sqlanywhere.blogspot.com/2008/02/todays-tip-case-sensitive-searching.html[^]

Don't expect it to be that fast.

Re: Case-Sensative Password Column in SQL Anywhere 10

PIEBALDconsult24-Feb-10 9:20

PIEBALDconsult

24-Feb-10 9:20

Passwords should not be case-sensitive, but you're welcome to if you want. You should not be storing clear-text passwords.

I recommend hashing the password (I use SHA-1). That should also allow case-sensitivity. Two birds; one stone.

Re: Case-Sensative Password Column in SQL Anywhere 10

MWRivera24-Feb-10 9:24

MWRivera

24-Feb-10 9:24

That sounds perfect for what I'm looking for.

Do you have an example or a link?

Thanks

Re: Case-Sensative Password Column in SQL Anywhere 10

PIEBALDconsult24-Feb-10 14:19

PIEBALDconsult

24-Feb-10 14:19

Here are my hashing methods:

public static string
Hash
(
    string Subject
)
{
    return ( Hash 
    ( 
        System.Text.Encoding.Unicode.GetBytes ( Subject ) 
    , 
        new System.Security.Cryptography.SHA1Managed() 
    ) ) ;
}

public static string
Hash
(
    string                                     Subject
,
    System.Security.Cryptography.HashAlgorithm Provider
)
{
    return ( Hash 
    ( 
        System.Text.Encoding.Unicode.GetBytes ( Subject ) 
    , 
        Provider
    ) ) ;
}

public static string
Hash
(
    byte[] Subject
)
{
    return ( Hash 
    ( 
        Subject 
    , 
        new System.Security.Cryptography.SHA1Managed() 
    ) ) ;
}

public static string
Hash
(
    byte[]                                     Subject
,
    System.Security.Cryptography.HashAlgorithm Provider
)
{
    System.Text.StringBuilder result = 
        new System.Text.StringBuilder ( Provider.OutputBlockSize ) ;

    foreach 
    ( 
        byte b 
    in 
        Provider.ComputeHash ( Subject ) 
    )
    {
        result.Append ( b.ToString ( "X2" ) ) ;
    }

    return ( result.ToString() ) ;
}

Re: Case-Sensative Password Column in SQL Anywhere 10

WoutL24-Feb-10 10:28

WoutL

24-Feb-10 10:28

How do you mean 'passwords should not be case-sensitive'. I think they should be! But your'e right about not storing them as clear text. They should be hashed indeed.

Wout Louwers

Re: Case-Sensative Password Column in SQL Anywhere 10

PIEBALDconsult24-Feb-10 14:10

PIEBALDconsult

24-Feb-10 14:10

Nothing in a user interface should be case-sensitive -- that's just my opinion after using OpenVMS for many years and then being thrown into Unix, Windows, VOS, etc. Dead | X|

Case-sensitivity is user-hostile; it does not serve the user.

Re: Case-Sensative Password Column in SQL Anywhere 10

WoutL24-Feb-10 19:50

WoutL

24-Feb-10 19:50

I never worked with Open-VMS. I think case-sensitve passwords are better for security.

Wout Louwers

Re: Case-Sensative Password Column in SQL Anywhere 10

David Skelly24-Feb-10 22:46

David Skelly

24-Feb-10 22:46

PIEBALDconsult wrote:
Passwords should not be case-sensitive

I'm glad you're not in charge of security at my bank.

I agree with the suggestion to hash the password in the database. However, be aware that the US Goverment has said that all federal applications should stop using SHA-1 as soon as practical as it is considered too weak and several authors have published attacks which exploit weaknesses in the algorithm.

Re: Case-Sensative Password Column in SQL Anywhere 10

MWRivera25-Feb-10 3:51

MWRivera

25-Feb-10 3:51

Thanks for the feedback guys.

After some discussion it's been asked of me to find out if there is any way of setting the actual column in the database up as a password column in some way. Is there any contraint/switch in sybase that will serve what I'm looking for?

Thanks again

Re: Case-Sensative Password Column in SQL Anywhere 10

David Skelly25-Feb-10 6:14

David Skelly

25-Feb-10 6:14

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.

Re: Case-Sensative Password Column in SQL Anywhere 10

MWRivera25-Feb-10 6:27

MWRivera

25-Feb-10 6:27

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

Mel

Re: Case-Sensative Password Column in SQL Anywhere 10

MWRivera3-Mar-10 4:37

MWRivera

3-Mar-10 4:37

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

Oracle Primary key

MsmVc24-Feb-10 0:01

MsmVc

24-Feb-10 0:01

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.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.