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.

Incorrect syntax near ')' SQL 2000

Hi

I keep receiving the error message Incorrect syntax near ')' whilst trying to save a stored procedure from within visual studio, I get the same error from within enterprise manager. The procedure is incomplete but valid, any idea appreciated. The procedure follows:

CREATE PROCEDURE dbo.ProcessComment
@.source AS VARCHAR(50) = NULL

AS
SET NOCOUNT ON

DECLARE @.sourceID AS INT
DECLARE @.counter AS INT
DECLARE @.Sources TABLE
(
sourceid AS INT,
lastconversation AS DATETIME
)

SET @.sourceID = -1
SET @.counter = -1

/*We need to see if this is a new or existing source*/
IF NOT @.source = NULL
BEGIN

INSERT @.Sources SELECT sourceID, lastconversation
FROM sources
WHERE name = @.source
ORDER BY lastconversation

--Do we have any matching sources or is this a new source?

--We need to pick the most likely source from the table
--The most likely source will be either one with an open conversation
--or the latest conversation or if we are lucky the only
--one in the result set

END

Thanks

Gav:

These two lines:

sourceid AS INT,
lastconversation AS DATETIME

need to be changed to:

sourceid AS INT,
lastconversation AS DATETIME

The compiler "thinks" that you are defining a computed column when you include the "AS" keyword.

Dave

|||Thanks Mugambo, that sorted me out :)

I wonder where I picked up that 'AS' syntax from in a table declaration, it just rolled off my fingertips onto the keyboard.

Thanks a lot.sql

In VS2005 where is Buisness Intelligence Project Option

Dear All,

I am using Visual studio .Net 2005. and i want to create report using sql server reporting services
for sql server 2000.
there is Buisness Intelligence Projects option in Visual studio .Net 2003 but this option
is not present in my vs2005 editor.
where is it exactaly resides or is there any way to do that.

Hi,

VS2005 does not have BI projects. These items are now part of SQL2005. You can install BI items from SQL2005 setup.

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.