Incorrect syntax near 'HelloWorld'

Hello,

I'm testing a CLR SP in SQL Server 2005 using Visual Basic .NET Express

The code is :

Imports System.Data.SqlServer
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Public Class SQLCLR
Public Shared Sub HelloWorld()
SqlContext.Pipe.Send("Hello World from SQLCLR !!")
End Sub
End Class

I built the project and copy the HelloWorld.dll to a local dir.
Then I registed the assembly in SQL SERVER:

create assembly HelloWorld from 'd:\xi\HelloWorld.dll' with permission_set = safe

This is successfull but when I execute:

create procedure HelloWorld as external name HelloWorld.SQLCLR.HelloWorld

Msg 6505, Level 16, State 1, Procedure HelloWorld, Line 1
Could not find Type 'SQLCLR' in assembly 'HelloWorld'.

I don't know what's happening since the name of the class is right (SQLCLR)

Any advice would appreciated...

I am guessing that you have used Visual Studio 2005 to develop your assembly. Visual Studio adds a default namespace and you need to incorporate that in the external name.
(I think the default namespace is same as the name of the assembly) Try:

create procedure HelloWorld as external name
HelloWorld.[HelloWorld.SQLCLR].HelloWorld

Look at project properties in VS and see if there is a default namespace. Either remove that or add the namespace in your external name.

Hope that helps.

Thanks,
-Vineet.

In What Order Index Sorts the Data?

Hi,

I want to ask a basic question, that is

IN WHAT ORDER A CLUSTERED INDEX SORT THE DATA IN THE COLUMN?

Somewhere in the MSDN library I read the following line:

"A clustered index physically sorts the table's contents in the order of the specified index columns"

But Sorting means it will be in ASCENDING ORDER (ASC) or It will be in DESCENDING ORDER (DESC)

So my question is lets suppose a column on which the cluistered index is defined and it contains character data liek abcd so in wht order it will sort the data alphabetically ASC or DESC

or

If the same above case with integer type of values, if column having integer values then in wht order the data in the table will be sorted.

?

Thanks..!!!

You are the one that has to speciy it. By default SS will use ASC.

Code Snippet

use tempdb

go

create table dbo.t1(

c1 int identity not null,

constraint pk_t1 primary key clustered (c1 ASC))

go

drop table dbo.t1

go

create table dbo.t1(

c1 int identity not null,

constraint pk_t1 primary key clustered (c1 DESC))

go

drop table dbo.t1

go

create table dbo.t1(

c1 int identity not null

)

go

create unique clustered index t1_c1_u_c_ix

on dbo.t1(c1 ASC)

go

drop index t1_c1_u_c_ix on dbo.t1

go

create unique clustered index t1_c1_u_c_ix

on dbo.t1(c1 DESC)

go

drop table dbo.t1

go

AMB|||

Ascending by default, but you can specify descinding if you'd like

|||

Hi,

I want to ask a basic question, that is

IN WHAT ORDER A CLUSTERED INDEX SORT THE DATA IN THE COLUMN?

Somewhere in the MSDN library I read the following line:

"A clustered index physically sorts the table's contents in the order of the specified index columns"

But Sorting means it will be in ASCENDING ORDER (ASC) or It will be in DESCENDING ORDER (DESC)

So my question is lets suppose a column on which the cluistered index is defined and it contains character data liek abcd so in wht order it will sort the data alphabetically ASC or DESC

or

If the same above case with integer type of values, if column having integer values then in wht order the data in the table will be sorted.

?

Thanks..!!!

--
PRASHANT PANDEY

Aargh!

Prashant,

Unfortunately, this information is simply wrong. There is absolutely no way to do anything at all in SQL Server to guarantee the physical order of the data. This is a good thing. If the data could be kept this way, you might have to move many gigabytes of data just to insert one row in the middle of a huge table. This would be terrible. "Ok, everyone move over to make room for the new person."

A clustered index does optimize the data storage for retrieval in the order of the clustered key columns, but if you want to be certain that data from a SELECT query comes back in a particular order, you absolutely must include an ORDER BY clause for that SELECT.

You may discover that without the order by, it works for years, and for millions of repetitions, but nevertheless, the order is not guaranteed, and suddenly when your table gets large, or you move to a different storage or processor configuration, or you apply a service pack, you may find you no longer see data in the order you expect.

Just in case all you were asking is in what logical order the data is organized when a column is specified as a key column, it is ascending order unless you specify otherwise with DESC.

Steve Kass

Drew University

http://www.stevekass.com

In Visual Basic and ADO.NET 2.0: How To Set IDENTITY_INSERT On

Hello,
From a Visual Basic program how would I turn on IDENTITY_INSERT?
Are there any warnings in doing this? What is the right time to do it?
How often should I do it? When should I turn it off?
Discussion started in microsoft.public.dotnet.languages.vb:
http://groups.google.com/group/micr...5def3c632f40314
Christopher Lusardi> From a Visual Basic program how would I turn on IDENTITY_INSERT?
> Are there any warnings in doing this? What is the right time to do it?
> How often should I do it? When should I turn it off?
If you really need to manually force a specific IDENTITY value, the proper
place to do this (imho) is as close to the database as possible (e.g. in a
stored procedure). This way you can have complete transactional and error
handling control over it. If your vb code exits before setting the property
back, you're going to leave the table in a bad state.
A|||Why would you want to do that? The purpose of the identity property is to ge
nerate a new value for
each new row you insert. Setting this option allow you to specify a value fo
r that column, but that
would defeat the purpose of having the identity attribute for the column in
the first place.
The setting can be useful for a dba to "repair" a lost row (need to get that
row back with the
original identity value), for instance.
How to set it? Execute below from ADO, and make sure it is on the same conne
ction as the later
INSERT statement (beware of connection pooling)
SET IDENTITY_INSERT tblname ON
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<clusardi2k@.aol.com> wrote in message news:1147879600.181591.192450@.i40g2000cwc.googlegroup
s.com...
> Hello,
> From a Visual Basic program how would I turn on IDENTITY_INSERT?
> Are there any warnings in doing this? What is the right time to do it?
> How often should I do it? When should I turn it off?
> Discussion started in microsoft.public.dotnet.languages.vb:
> http://groups.google.com/group/micr...5def3c632f40314
> Christopher Lusardi
>|||> The setting can be useful for a dba to "repair" a lost row (need to get
> that row back with the original identity value), for instance.
The only place I use it is in a specific case where we are migrating data
from an old system to a completely rewritten one and, ly, the previous
maintainers actually attached value to the identity values, placing it in
scripts, distributing details to clients, etc. So I needed an easy way to
create similar entities in the system that had the same identifier as the
old system. It sucks, and I wouldn't want it to be a normal operative
process.
A|||Tibor Karaszi wrote:
> Why would you want to do that? The purpose of the identity property is to
generate a new value for
> each new row you insert. Setting this option allow you to specify a value
for that column, but that
> would defeat the purpose of having the identity attribute for the column i
n the first place.
>
Summarizing from my above reference:
When I do the below within VB, I get the error message:
"Cannot insert explict value for identity column in table
'Employees' when IDENTITY_INSERT is set to OFF."
To get this message, I click my Add button to add a new row to the
database, and then I click the Update button to save the new database
to the external memory.
The functions I used are below.
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAdd.Click
Dim drNew As DataRow
drNew = dsAdoSbs.Employees.NewRow()
drNew.Item("FirstName") = "New First"
drNew.Item("LastName") = "New Last"
dsAdoSbs.Employees.Rows.Add(drNew)
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnUpdate.Click
daEmployees.Update(dsAdoSbs.Employees)
End Sub
When I look at the properties of the data adapter for daEmployees above
nothing jumps out at me?
When I start vb and view "Server Explorer", I see:
- Data Connections
- chrislusardi\sqlexpress.AdoStepByStep.dbo
+ Database Diagrams
- Tables
- Employees
LastName
FirstName
..
I can't find "sqlexpress.AdoStepByStep" on my PC with a search on the C
drive.
With a I double click on Employees, I see a small yellow light key next
to a
column indicating it's the primary key column etc. When I click on that
yellow key, nothing in the properties jump out and say here's the
error.
Chris Lusardi|||This is an ADO issue. For some reason, ADO doesn't realize that you have an
identity column, so ADO
generates an INSERT statement where it specifies a value for that identity c
olumn. Unless someone
with ADO knowledge jumps in here, I suggest you post this in an appropriate
ADO newsgroup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<clusardi2k@.aol.com> wrote in message news:1147884319.815223.323170@.i40g2000cwc.googlegroup
s.com...
> Tibor Karaszi wrote:
> Summarizing from my above reference:
> When I do the below within VB, I get the error message:
> "Cannot insert explict value for identity column in table
> 'Employees' when IDENTITY_INSERT is set to OFF."
> To get this message, I click my Add button to add a new row to the
> database, and then I click the Update button to save the new database
> to the external memory.
> The functions I used are below.
> Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles btnAdd.Click
> Dim drNew As DataRow
> drNew = dsAdoSbs.Employees.NewRow()
> drNew.Item("FirstName") = "New First"
> drNew.Item("LastName") = "New Last"
> dsAdoSbs.Employees.Rows.Add(drNew)
> End Sub
>
> Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e
> As System.EventArgs) Handles btnUpdate.Click
> daEmployees.Update(dsAdoSbs.Employees)
> End Sub
> When I look at the properties of the data adapter for daEmployees above
> nothing jumps out at me?
> When I start vb and view "Server Explorer", I see:
> - Data Connections
> - chrislusardi\sqlexpress.AdoStepByStep.dbo
> + Database Diagrams
> - Tables
> - Employees
> LastName
> FirstName
> ...
> I can't find "sqlexpress.AdoStepByStep" on my PC with a search on the C
> drive.
> With a I double click on Employees, I see a small yellow light key next
> to a
> column indicating it's the primary key column etc. When I click on that
> yellow key, nothing in the properties jump out and say here's the
> error.
> Chris Lusardi
>|||> This is an ADO issue. For some reason, ADO doesn't realize that you have
> an identity column, so ADO generates an INSERT statement where it
> specifies a value for that identity column. Unless someone with ADO
> knowledge jumps in here, I suggest you post this in an appropriate ADO
> newsgroup.
Or call a stored procedure instead of all this built-in, double-click, drag
and drop stuff that is not as generic or useful as it would appear.