create site search using sql server "full text search"

would you use sql server "full text search" feature as your site index? from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on...

so i thought create site search table, with the columns:

id, text, url

and to write every thing to this table.

but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server...

what do you think?

Full text search is handled by SQL Server itslef (via an external windows service) and is a good solution but you need to go through pros and cons and take care about when to generate the indexes and what columns to index. Refer these articles for details:

http://www.eggheadcafe.com/articles/20010422.asp

http://www.developer.com/db/article.php/3446891

Hope this helps,

Vivek

Create new database in SQL Server 2005 Express Edition

Hello,

I use the SQL Server 2005 Express Edition. I want to create a new database, but I cant find where and how.
Can anyone provide me with starter's tips and/or a good tutorial?

Thanks!

If you're using the VS 2005, then simply right-click on your Project (from Solution Explorer), Add New Item, and choose Sql Database.|||Or if you don't have a project yet, you can do it from within the server explorer as well.|||

I use Visual Web Developer Express 2005. With this I was also able to create a new database.
I can add tables, views etc.
But no triggers...do you guys have an idea how I can add a new trigger?!??

Create login and user

Hi,

I’m new in this.

I’m trying to create a user with the following code, but in the yellow part it throw an error saying the user can’t be created. Please if somebody can say what the problem is or correct the problem for me I will be thankful:

Public Sub CreateUser()

Dim conn As New ServerConnection("STATION01\SQLEXPRESS", "admin", "a9078564")

'conn.DatabaseName = "VideoDB"

Dim myServer As New Server(conn)

If Not myServer.Logins.Contains(TextBox2.Text) Then

Dim db_login As New Login(myServer, TextBox2.Text)

db_login.LoginType = LoginType.SqlLogin

db_login.Create(TextBox3.Text)

Dim sys_database As New Database(myServer, "VideoDB")

Dim db_user As New User(sys_database, TextBox2.Text)

db_user.Login = TextBox2.Text

db_user.Create()

If CheckBox18.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_owner")

Else

Dim SelectPrivilege As New ObjectPermissionSet

SelectPrivilege.Select = True

Dim InsertPrivilege As New ObjectPermissionSet

InsertPrivilege.Insert = True

Dim UpdatePrivilege As New ObjectPermissionSet

UpdatePrivilege.Update = True

Dim DeletePrivilege As New ObjectPermissionSet

DeletePrivilege.Delete = True

Dim db_table As New Table

db_table.Schema = "admin"

db_table.Name = "customers"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox2.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox3.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox4.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

db_table.Name = "capturepicture"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

db_table.Name = "items"

If CheckBox5.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox6.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox7.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox8.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

If CheckBox9.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_securityadmin")

db_user.AddToRole("db_accessadmin")

db_user.AddToRole("CreateUser")

End If

db_table.Name = "properties"

If CheckBox10.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox11.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox12.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If RadioButton1.Checked Then

db_user.AddToRole("TransactionsFullAccess")

Else

db_user.AddToRole("TransactionsRestrictedAccess")

End If

If CheckBox15.Checked Then db_user.AddToRole("Promotions")

If CheckBox18.Checked Then db_user.AddToRole("History")

End If

MsgBox(db_login.Script)

MsgBox(db_user.Script)

End If

End Sub

Regarding the samples located on http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=195387&SiteID=1 you did just fine beside the logon Type. Which error do you get during the creation of the user ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The only thing I see wrong with your code is four lines above the highlighted line, where the code is:

db_login.Create(TextBox3.Text)

Now, everywhere else you're referring to TextBox2.Text, but in the create of the login you're using TextBox3.Text. It may be failing due to that mismatch. Otherwise the code looks correct.

|||

Hi,

In the textbox2 I refer the username in the textbox3 I refer the password.

I change the blue part and know it creates the user, but know the problem is in the tables privileges. It throws this error:

object Microsoft.SqlServer.Management.Smo.Table does not live under Server.

Public Sub CreateUser()

Dim conn As New ServerConnection("STATION01\SQLEXPRESS", "admin", "a9078564")

Dim myServer As New Server(conn)

If Not myServer.Logins.Contains(TextBox2.Text) Then

Dim db_login As New Login(myServer, TextBox2.Text)

db_login.LoginType = LoginType.SqlLogin

db_login.Create(TextBox3.Text)

Dim sys_database As Database = myServer.Databases("VideoDB")

Dim db_user As New User(sys_database, TextBox2.Text)

db_user.Login = TextBox2.Text

db_user.Create()

If CheckBox18.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_owner")

Else

Dim SelectPrivilege As New ObjectPermissionSet

SelectPrivilege.Select = True

Dim InsertPrivilege As New ObjectPermissionSet

InsertPrivilege.Insert = True

Dim UpdatePrivilege As New ObjectPermissionSet

UpdatePrivilege.Update = True

Dim DeletePrivilege As New ObjectPermissionSet

DeletePrivilege.Delete = True

Dim db_table As New Table

db_table.Schema = "admin"

db_table.Name = "customers"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox2.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox3.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox4.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

db_table.Name = "capturepicture"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

db_table.Name = "items"

If CheckBox5.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox6.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox7.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox8.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

If CheckBox9.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_securityadmin")

db_user.AddToRole("db_accessadmin")

db_user.AddToRole("CreateUser")

End If

db_table.Name = "properties"

If CheckBox10.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox11.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox12.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If RadioButton1.Checked Then

db_user.AddToRole("TransactionsFullAccess")

Else

db_user.AddToRole("TransactionsRestrictedAccess")

End If

If CheckBox15.Checked Then db_user.AddToRole("Promotions")

If CheckBox18.Checked Then db_user.AddToRole("History")

End If

MsgBox(db_login.Script)

MsgBox(db_user.Script)

End If

End Sub

|||

Hi,

Now it Works. Thanks for the help.

Code:

Public Sub CreateUser()

‘stablish connection with sqlserver authentication

Dim conn As New ServerConnection("STATION01\SQLEXPRESS", "admin", "a9078564")

Dim myServer As New Server(conn)

‘Verify if the user already exist

If Not myServer.Logins.Contains(TextBox2.Text) Then

‘Create the login

Dim db_login As New Login(myServer, TextBox2.Text)

db_login.LoginType = LoginType.SqlLogin

db_login.Create(TextBox3.Text, LoginCreateOptions.None)

Dim sys_database As Database = myServer.Databases("VideoDB")

‘Create the user

Dim db_user As New User(sys_database, TextBox2.Text)

db_user.Login = TextBox2.Text

db_user.UserType = UserType.SqlLogin

db_user.Create()

If CheckBox18.Checked Then

‘Grant roles

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_owner")

Else

‘Grant privileges

Dim SelectPrivilege As New ObjectPermissionSet

SelectPrivilege.Select = True

Dim InsertPrivilege As New ObjectPermissionSet

InsertPrivilege.Insert = True

Dim UpdatePrivilege As New ObjectPermissionSet

UpdatePrivilege.Update = True

Dim DeletePrivilege As New ObjectPermissionSet

DeletePrivilege.Delete = True

Dim db_table As New Table(sys_database, "customers", "admin")

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox2.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox3.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox4.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

db_table = New Table(sys_database, "capturepicture", "admin")

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

db_table = New Table(sys_database, "items", "admin")

If CheckBox5.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox6.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox7.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox8.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

If CheckBox9.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_securityadmin")

db_user.AddToRole("db_accessadmin")

db_user.AddToRole("CreateUser")

End If

db_table = New Table(sys_database, "properties", "admin")

If CheckBox10.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox11.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox12.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If RadioButton1.Checked Then

db_user.AddToRole("TransactionsFullAccess")

Else

db_user.AddToRole("TransactionsRestrictedAccess")

End If

If CheckBox15.Checked Then db_user.AddToRole("Promotions")

If CheckBox18.Checked Then db_user.AddToRole("History")

End If

End If

End Sub

CREATE ENDPOINT (Transact-SQL)

hello all i am new here,

my question is how i can activate http in sql server 2005

i want to run a helpdesk application but i cant make a new database.

it has something to do with create endpoint, i have no knowlege off sql server 2005 its not my field

can someone help me please?

It seems like you may have 2 separate problems. One is creating a new database and the other is creating an endpoint. If the root problem of the "helpdesk application" is creating the new database, I suggest posting a new forum posting with the error you get when you try to create the database. That will help people understand what the problem is and be able to suggest solutions.

Regarding creating an endpoint, I assume you are already able to connect to SQL Server. You will need SQL administrator or equivalent permissions to create the endpoints. For additional information regarding creating endpoints, please refer to Books Online "CREATE ENDPOINT" topic (http://msdn2.microsoft.com/en-us/library/ms181591.aspx).

If you have problems creating the endpoint outside of the "helpdesk application" (ie. using SQL Management Studio), please post the error you get from the server.

Jimmy|||

thank you jimmy,

i have found a workaround by installing a different sqlserver

thanks anyway

paul