You're right, this is also a storage format. However, as far as I understood the question the application is Microsoft's enterprise manager or management studio -> you do not have the possiblitiy to change the display format without changing the storage format. If the original question did not regard to pre-built standard software but to individually developed software I aggree to you. Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

Hi! Check my blog at http://www.cubido.at/Blog/tabid/176/EntryID/34/Default.aspx. There I show how you can encrypt and decrypt the password column with SQL2005. If you do not have SQL2005 but 2000 you do not have these smart encryption functions built into the DB. You would have to implement encryption yourself (e. g. using .NET's encryption functions). In fact for a password it would be enough to calculate a (salted) hash (see http://en.wikipedia.org/wiki/Salt_%28cryptography%29 for details) -> no encryption needed. A completly unsecure solution that just displays the password in binary instead of cleartext is to change the data type of the password column to varbinary. A SELECT will then just show hex values. Again, this is NOT encryption. Everyone how is able to write SQL can write cast(password as varchar) and sees the password. However, sometimes it is enough to just changing the display format of the password to binary. Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

Hi! It is not supported to build and execute a dynamic query in user defined functions. UDFs have to be deterministic -> no exec( @MyVar ) allowed. Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

Hi! Your stored proc could look like this:

create procedure AddNewPerson
@Name varchar(50)
as
set nocount on

insert into StaffAccountTable ( StaffName ) values ( @Name )
insert into Login ( StaffID, ... ) values ( @@identity, ... )
go

Hope this helps. Regards, Rainer Rainer Stropek Visit my blog at http://www.cubido.at/rainers

If you ask me no chance to do this in a user defined function. You would need dynamic SQL with exec( @stmt ) and that is not allowed in UDFs. Can't you use a stored proc? You can use dynamic SQL there. Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

I would suggest to add the isnumeric function if you are not absolutely sure that the string ALWAYS contains valid ints:

select cast( case when isnumeric(ColumnName)=1 then ColumnName else NULL end as int ) from TableName

Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

Hi! You have to set your SQL 2005 service to mixed mode authentication (do that in the property window of your server in management studio: Right-click your server in object explorer, Properties, Security, SQL Server and Windows Authentication mode). If you do not change to mixed mode you cannot use sql authentication. Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

If you use SQL2005 you should use varbinary(max). image should not be used any more. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

As far as I know this is not possible. Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

Hi! It depends if you want to combine the content within a SQL statement without transferring the data permanently or if you want to create a third table that physically contains the combined data. If you need a query and you do not want to tranfer the data 1. create a linked server from machine one to two (or the other way round; you can do this in SQL Server management studio (2005) or Enterprise Manager (2000) 2. select * from DBTest.dbo.DTTable1 union all two.DBTest.dbo.DTTable1 As an alternative to 1 you can also use OPENQUERY or OPENROWSET (see SQL Server Books Online for details). If you want to move the combined data into a third table use DTS (SQL 2000) or SSIS (SQL 2005). With these tools it is quite easy to move data from one table into another. Hope this helps. Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

Hi! Don't forget that there is another big difference between Sqlxxx types and the normal types: Sqlxxx types can be null (Property Null). If your fields support null-values you should use Sqlxxx types. Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

If you have SQL 2005 you could also write a short user-defined aggregation function in C# that concats strings. Regard, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

Hi! Ok, I think I understand your problem. You have to change my SQL generation query like this:

select 'create view MyHelperView as '
union all
select 'select [name], [year], [date], '
+''''+table_name+''' as tablename from '+table_schema+'.'
+table_name+' union all '
from INFORMATION_SCHEMA.TABLES

This query generates a helper view that you can use in your program. It contains a new colum named "tablename" that you can display. Therefore your query could look like this:

select tablename, name from MyHelperView where date = ...

Hope this helps! Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

Hi! You can simplify this statement:

Declare @au_lname char(30)
Select @au_lname = ''

select title, price, Name = substring (au_lname, 1,40), type
from titleauthor ta
JOIN authors a on ta.au_id = a.au_id
JOIN titles t on ta.title_id = t.title_id
where RTRIM(@au_lname) = ''
or RTRIM(UPPER(@au_lname)) = RTRIM(UPPER(au_lname))

Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

Hi! Sorry, I don't think that your query will work. You cannot insert into a table variable from inside a dynamic query. Try the following:

declare @test table ( MyId int )
insert into @test values (5)

declare @stmt varchar(128)
set @stmt = 'insert into @test values (6)'
exec( @stmt )

The dynamically executed statment will not work because it does not know about the table var @test. Additionally I saw that you forgot the ( ) in your exec-statement. exec @querystring cannot execute a dynamic query; you have to write exec ( @querystring ). Otherwise SQL Server will think you want to execute a stored procedure with the name stored in @querystring. See Transact-SQL-Reference for EXECUTE in Books Online for details. Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

Hi! You can generate the query you need using the following SQL-Statement:

select 'select [name], [year], [date] from '+table_schema+'.'
+table_name+' union all '
from INFORMATION_SCHEMA.TABLES

Execute this statement with result type = text, copy the result to a new query windoe and delete the last "union all". It will give you a union all of all your tables -> your 50 tables combined into one. Hope this helps. Regards, Rainer Rainer Stropek Visit my blog at http://www.cubido.at/rainers

You don't need CLR stored procs to encrypt or decrypt. SQL 2005 has encryption built in. Take a look at my blog at http://www.cubido.at/Blog/tabid/176/EntryID/34/Default.aspx. There you can find an example including a mind map describing SQL 2005 encryption functions. Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/Blog/tabid/176/BlogID/4/Default.aspx

Hi! This query shows how to do that: select e1.EmployeeID, e1.BirthDate, cast(e1.BirthDate-( select top 1 e2.BirthDate from HumanResources.Employee e2 where e2.BirthDate You can try it with SQL Server's AdventureWorks sample DB. Rainer Stropek Visit my blog at http://www.cubido.at/Blog/tabid/176/BlogID/4/Default.aspx

Hi dadax! Check my blog article at http://www.cubido.at/Blog/tabid/176/EntryID/34/Default.aspx. It describes how to do that using SQL Server's new encryption/decryption feature. Regards, Rainer. Rainer Stropek cubido business solutions gmbh Email r.stropek@cubido.at Visit my blog at http://www.cubido.at/Blog/tabid/176/BlogID/4/Default.aspx

Hi! In SQL 2005 you should use CTE and the new ranking functions; write with EmpCTE ( EmpNo, ..., Sal, SalRank ) as ( select EmpNo, ..., Sal, dense_rank() over ( order by Sal desc ) ) select * from EmpCTE where SalRank=5; Rainer Stropek cubido business solutions gmbh Email r.stropek@cubido.at Visit my blog at http://www.cubido.at/Blog/tabid/176/BlogID/4/Default.aspx