Database

Re: How can I store PDB files?

3-Dec-08 1:19

Take a look at the Binary and VarBinary datatypes.

Giorgi Dalakishvili3-Dec-08 4:35

Giorgi Dalakishvili

3-Dec-08 4:35

Here you are: How To Read and Write BLOB Data by Using ADO.NET with Visual C# .NET[^]

Giorgi Dalakishvili

#region signature
My Articles

Asynchronous Registry Notification Using Strongly-typed WMI Classes in .NET [^]

My blog
#endregion

Insert before update -the first data- in a historical table

foryou3-Dec-08 0:11

Re: Insert before update -the first data- in a historical table

3-Dec-08 0:11

Hi!

I work with Sqlserver2005 and I have a table which I update, I need to insert the data before update in a table History.
is a way to do that.
Thank you in advance.

Blue_Boy3-Dec-08 0:21

Re: Insert before update -the first data- in a historical table

3-Dec-08 0:21

Insert into TableName Values('val1','val2'.....)
Update TableName set Col1='val1', Col2='val2'..... where ColName=ValCondition

For better answer,post more detailed questin.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

foryou3-Dec-08 0:50

Re: Insert before update -the first data- in a historical table

3-Dec-08 0:50

Thanks Blue_Boy;
sorry my english is bad !
I have two tables Table1 and historical_table1. With the same properties and the data are displayed in a gridview in editable mode . when the user modify a line of table1 I want that the first data (before update) will be inserted in historical_table1 automatically.

Thanks .

Blue_Boy3-Dec-08 0:57

Re: Insert before update -the first data- in a historical table

3-Dec-08 0:57

Use triggers[^].
While updating data in table Table1 (I sugggest you to rename this table 'Table1' and give it any meaningful name) execute trigger which do insert in historical_table1.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

foryou3-Dec-08 1:22

Re: Insert before update -the first data- in a historical table

3-Dec-08 1:22

Thanks a lot Blue_Boy;
I must document on triggers because its the first time I heard trigger in database, thank you very much.

if you know a course for beginners on the triggers thank you pass me the link. Smile | :)

Blue_Boy3-Dec-08 1:41

Re: Insert before update -the first data- in a historical table

3-Dec-08 1:41

No problem,I am glad to help you.

Check this link.[^]

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

foryou3-Dec-08 2:50

Re: Insert before update -the first data- in a historical table

3-Dec-08 2:50

Merci beaucoup Blue_Boy.

Blue_Boy3-Dec-08 5:06

Re: Create A USER in Databse

3-Dec-08 5:06

You are welcome. Smile | :)

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Create A USER in Databse

kibromg3-Dec-08 0:01

kibromg

3-Dec-08 0:01

Hi All,

I would like to create a user when he logs in , i can hide all the tables that has been Created Before.All the database Table and Stored Procedures will be Hidden.

Can you please advice?

Many thanks

Ashfield3-Dec-08 7:13

Ashfield

3-Dec-08 7:13

He cannot be logging directly into the database to do this, so you need to create a login page and use a known user name to see if he already exists. If not then you the need to create the login and user with the appropriate permissions.

Bob
Ashfield Consultants Ltd

Replace function

Jay Royall2-Dec-08 23:47

2-Dec-08 23:47

Hi,

I am using SQL Server 2005 and have a question regarding the Replace function. I have a 'select' query looking something like this:

SELECT tblContacts.*
FROM tblContacts
WHERE tblContacts.[Surname] + tblContacts.[Firstname] LIKE '%Smith (Hon. Member)John%'

('Smith (Hon. Member)John' will come from the user's input)

Now, the end users do not want to have to type in 'Smith (Hon. Member)John', they just want to type SmithHonMemberJohn, that is, the full name and membership type without any spaces, brackets or full stops (periods).

I have looked at the replace function to replace characters but is it possible to replace more than 1 type of character? That is, the replcae function will replace all instances of a certain character, for instance, commas. But is it possible to specify more than 1 chacter type, for instance commas, brackets and spaces?

Hope I have explained this OK. Thanks in advance for any help.

RGTuffin3-Dec-08 1:19

RGTuffin

3-Dec-08 1:19

Not ideal, but you can nest the replace statments for a quick fix:

replace(replace(replace(replace('Smith (Hon. Member)John','(',''),')',''),'.',''),' ','')

produces: SmithHonMemberJohn

otherwise you could always write a function to remove any non alphanumeric characters.

I don't know of any pattern matching within the replace statement though.

Jay Royall3-Dec-08 1:35

3-Dec-08 1:35

I like the idea of a SQL Server function to remove any non alpha characters but, my query is being dynamically built within the Windows application rather than being a stored procedure. Am I able to call a SQL Server function in this way? If not then the nested replace will have to suffice!

Ben Fair3-Dec-08 3:54

Ben Fair

3-Dec-08 3:54

Yes, you can call it that way. If you make a SQL Server scalar function for this purpose, let's say it's named 'RemoveNonAlphaChars'. Your SELECT would use it like so:

SELECT tblContacts.* 
FROM tblContacts
WHERE dbo.RemoveNonAlphaChars(tblContacts.[Surname] + tblContacts.[Firstname]) LIKE '%SmithHonMemberJohn%'

Keep It Simple Stupid! (KISS)

Jay Royall3-Dec-08 4:17

3-Dec-08 4:17

Great

Thank you both for your help.

Duplicate in join

gnjunge2-Dec-08 2:25

gnjunge

2-Dec-08 2:25

Hi,

This must be real simple, but I'm having a blackout.
I have two tables:

Names:

ID    Name
1     John
2     Peter
3     Carl
4     Bernard

Nicknames:

ID   NameID    NickName
1    1         Bonny
2    1         Bobby
3    1         Jo
4    2         Pete
5    3         Bab
6    3         Foo

Now I have a search running, which looks for a certain string in both the nickname field as well as in the name field.
But it should return only 1 record per name (doesn't matter which nickname), so if the seachstring would be 'b%' it would return the following.

NameID NickNameID Name            Nickname
4      null       Bernard         null
1      1          John            Bonny
3      5          Bernard         Bab

I fall on the distinct record thing for the nicknames, I have a dirty subselect, but since the query is actually much bigger, and the table contains 100.000 records, it isn't the right solution.

Does anybody have the correct solution to this issue?

Wendelius2-Dec-08 2:52

Wendelius

2-Dec-08 2:52

You say that the select should return only one nickname. In your example, why did the select return Bonny for Bernard, not Bobby. Is there somekind of logic or is it simply random.

The need to optimize rises from a bad design.My articles[^]

gnjunge2-Dec-08 2:57

gnjunge

2-Dec-08 2:57

Random, I don't care which one he returns.

Re: Duplicate in join [modified]

Wendelius2-Dec-08 3:11

Wendelius

2-Dec-08 3:11

Okey, then you could use normal outer join between the tables and for example check that there's no greater nicknameid for the same name (using for example correlated not exists clause).

The need to optimize rises from a bad design.My articles[^]

Blue_Boy2-Dec-08 9:11

2-Dec-08 9:11

gnjunge wrote:
I have a dirty subselect

I hope this T-SQL is not "dirty" but I couldn't make shorter,I hope too this T-SQL will help you.

select id,<br />
(select top 1 A.ID from nicknames as A where A.nameID = names.ID) as NicknameID,<br />
name,<br />
(select top 1 A.nickname from nicknames as A where A.nameID = names.ID) as Nickname<br />
from names<br />
where (names.name like 'b%')<br />
or names.id in (select C.nameid from nicknames as C where C.nickname like 'b%' ) </code><br />
<br />
Result is :<br />
<br />
<code>1	1	John	Bonny<br />
3	5	Carl	Bab<br />
4	NULL	Bernard	NULL

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.