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 \

Hi

I'm trying to lookup a sigle value from an MS SQL Express database. However, I get an error - Incorrect Syntax near '\' - when executing the code. The line in bold below is what is highlighted on the error I get back. Please Help!!

The code is below

Thanks

Dim oSQLConn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection()oSQLConn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SDCentral.mdf;Integrated Security=True;User Instance=True"oSQLConn.Open() Dim ValExtract As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("SELECT * FROM ProjectsQRY", oSQLConn) Dim Get1 As Int32 = CInt(ValExtract.ExecuteScalar()) Me.Active1.Text = Get1

oSQLConn.Close()

Hi,

What is the first column in your output, check it, if its any string column that might be the reason.

|||

Hi

Thanks. The first colomn is IsNull([CNT],0)

|||

Is ProjectsQRY a table or a view?

|||

Hi

It was a view, but I found the problem - it was in the view. I was fooled by the obscure error message. Thanks for your help

Inconsistency between Data Source Designer and underlying code

I have 2 data sources that have recently been updated from SQL Express to full versions of 2005. The connection strings have been changed, and the changes appear in the code, but the data source designer still shows the SQLExpress portion of the connection string. This seems to be fouling up SSIS packages that are using these data sources. Has anyone else encountered this? If so, what can I do to fix this issue?Can you recreate them?|||Are you using configurations? If so, have you updated them to reflect the right connect strings?|||Turns out the problem was this: the project was checked out by the person who created the datasources. When he checked the datasources in, he did NOT check the project back in. The project's definition contained the incorrect connection strings. Once the project was checked in, that issue was resolved. Thanks for your responses.

Including SQL Server Express as a nested MSI install

I'm working on an MSI-based install that installs a suite of applications. One of the applications requires SQL Server 2005 Express to be installed or already on the machine, but if the user doesn't want that app installed, I don't want to have to bother with the SQL Server issue; however, if that app is to be installed and SQL Server is not already installed, I want to be able to have it installed automatically during our install in order to make it easier for our end users. The obvious answer to this is to launch the SQL Server Express install as a nested MSI install at the appropriate point in our install. Since the SQL Server install is so complex, I was wondering if this would work if I were to launch the SQL Server install's setup.exe; or do I have to launch one of its dozen MSI files? If it's the latter, which one should my install launch? (Or is the SQL Server Express install simply too complex to be included in my install in this way? My plan is to have the SQL Server install sitting somewhere on our install CD - we've already obtained a redistribution licence - and have our install access it from there.) And will SQL Server Express appear as a separate entry in the Add/Remove Programs applet if it's installed this way? (I'd like to be able to leave SQL Server in place when uninstalling our application suite - or even just the one app that needs it - and allow SQL Server to be uninstalled separately if the user wishes.)Moving to the SQL Express forum, this is not a SSIS issue.|||

Hi Rob,

Yes, you can install SQL Express as part of your applications installation, but you can not nest it inside your existing MSI. Windows Installer does not allow nesting of MSIs, so you have to create a wrapper that will handle running both the SQL Express installation and your own application MSI.

If you are building your application using Visual Studio 2005, the VS Bootstrapper is already designed to do this for you. Just add SQL Express from the list of Pre-requisites and VS will build your setup to automatically install SQL Express along side your application. If you're trying to roll you own, there are a number of different resources available to help you do this.

Check out the FAQ that I've just posted at the top of this forum, you will find links to information about installing SQL Express in quite mode and detecting if SQL Express is already on the computer.|||It would appear, then, that I'm in a catch 22 situation. As I mentioned in my original posting, my install is MSI-based, and it installs a vertical market suite of applications, only one of which needs SQL Server Express to be installed. As I also mentioned, I don't want to have to deal with the SQL Server issue if the user doesn't want to install that particular application. This suggests that SQL Server be installed after the application suite is installed. However, if the application in question is installed, the install has to start it once it's installed, meaning that SQL Server must be installed first. These two conflicting needs point to the need for SQL Server to be installed at an appropriate point during my suite install.

I'm using Wise for Windows Installer to develop my installer, and because I want only one entry for the entire application suite in the ARP applet, I don't think separating the install for each application in the suite into a separate MSI would be the way to go. Also, if each application were installed by a separate MSI, wouldn't each MSI have its own GUI. I'd like to have one GUI for the entire suite, preferably without having to roll my own dialogues from scratch. Hence, the install was constructed as a single MSI.

Is there no way to launch the SQL Server Express install as a nested install using one of the MSIs that come in the package, or are those MSIs inextricably tied into the setup.exe wrapper?|||

Hi Rob,

No, you can not nest MSI installation. This is a limitation of the Windows Installer technology.

I'm not sure you understood all of my post; it is totally possible to install SQL Server in quite mode, so that it doesn't show any UI at all. Read the quite mode install FAQ and the embedding paper to learn more about how to deploy SQL Express with your own application.

SQL Express will always install as a separate item in the ARP list. We have to do this for the purpose of discovery and patching. There is a bunch of technical mumbo jumbo, but the short version is that if you could install SQL Express so that it didn't show up in ARP, then we couldn't detect it from Microsoft Update, and there would be no facility for patching it. In the event that there is a security issue that needs to be patched, we must be able to detect SQL Express to patch it. If you hid SQL Express, you would be putting your customers at risk, which I'm sure you don't want to do.

Thousands of developers accomplish exactly what you are trying to do using a wrapper program to chain together multiple MSI based installations. You will need to write a wrapper program that contains the logic to determine which components need to be installed. In your case, you would simple have a wrapper that would allow customers to pick which parts of your application you want to install, if they pick the part that requires SQL Express, then your wrapper checks for SQL Express, and if it is not there, installs it. The wrapper acts as a unifying UI driver. (This is exactly what SQL Server does, we have a wrapper, Setup.exe, that combines multiple MSIs into a single experience.)

It's possible that Wise might even provide the ability to create wrapper programs, I'm sure there are a number of Wise support forums out there where you could find others who are facing this exact same issue. Start with the Wise web site and I'll be there is already a solution.

Regards,

Mike

Including Sql Express DB with Winforms Application

We are thinking of using a Sql Server Express database (.mdb.mdf) as a data resource in a Windows Forms application that we will be distributing to clients. When the application is built, the mdb file is copied to the bin directory.

In order for clients to access this database, will they need to have an instance of the SQLEXPRESS server installed on their computer? Or since the .mdb.mdf file is being accessed from a .Net 2.0 application, there is no need to install a separate db component?

If I do have to separately install the db component on the client end,
is this the way to go about it? This? Or is there a different/better way?

If your file really is .mdb, this is an Access file, and your program will connect to it with out any trouble.

SQL Express files are .mdf

For SQL Express, just drop your data file into your resources directory, and it will be published with your project. (This works with any file you want to include in your project, like graphic files used in your reports, etc.)

Set your Build Action to "Content" and make sure you select "Copy if newer", and not "Copy Always" as this will erase any data you have everytime you test your program.

- Richard.

|||

When you do distribute you can package up the SQL Engine (SQL Express) into the installer... but yes the clients do need the engine installed on there system. Now depending on how you are access the database you have several options. With express you have two ways of connecting to the database, the first is to just install the engine and attach the files to that engine, then you can connect to the database from the client as well as from other clients.... ie remote clients (After turning on the remote Access) using the one database server. The other option is to use a User Instance of the database. With this method the database is attached to the engine by the SQL Native client at runtime. This means that when you start the application that you have developed, the SQL Native client connects using the Connection string you provide (With the Attach option) and attaches the DB.... when the app is finished with it it will detatch the database. One problem with this is that generally it is set to single user and only the security context of the application running the App will be able to access the database.

So in summary if only the client needs to connect to the DB at the one time the best option would be to dist the sql engine and client app seperatly and allow the sql native client to do it's job and attach the db.... but if more then one client needs to access the same database at the one time You should attach the db your self and use the standard client server setup.

Hope this helps..

|||Richard - Thanks, you are right. I meant to say "mdf"|||Thanks Glenn.

In my situation, we will have an instance of SQL Express installed on the client machine, with only that one client connecting to the database. So you think that I should connect to the DB with a connection string that attaches the DB, and when the APP is finished it will detach the DB? What is the format of such a string. Right now I am using a connection string of the following format:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\db.mdf;Database=DBName;Integrated Security=False;User Instance=False;User ID=USERNAME;Password=PASSWORD;Connect Timeout=30;

As I understand it, this connection string will attach the DB to the database with the name provided in the "Database" parameter. In the future, the DB will already be attached to the server under that name. How should the connection string be modified so that when the APP is finished it will detach the DB? Will this happen when the application closes? Or does this happen before and after every single database interaction (thus, while the application is open, you could be attaching and detaching the database thousands of times)? If so, what kind of extra overhead does this constant attaching and detaching cause?

Additionally, we would like to prevent any access to the database unless it is with the Username and Password (ie: we do not want them to access the database directly, rather through the application that we provide. In the application itself, the connection string will be encrypted). Is there a way to prevent the user from connecting to the database through the SA account or the Admin account for the computer, to ensure that all connections are done only using the username and password?

In VB Express 2005, I cant save any record in database of sql server.

I was using the msdn tutorial regarding sql database for vb2005 express. In that tutorial, I followed the steps as listed. But the problem is that the save button in my application during run time, it does not work. Furthermore, the code for for saving a record during run time does not work as well.

I have reinstall sql 2005 server and vb2005 express but of no use.

I have winxp with system of pentium 4.

What I should do to solve this dilemma.

Faisal.

You may want to check with this group of people:

http://www.tutorialized.com/tutorials/Visual-Basic/Database-Related/1

Buck Woody

In VB Express 2005, I cant save any record in database of sql server.

I was using the msdn tutorial regarding sql database for vb2005 express. In that tutorial, I followed the steps as listed. But the problem is that the save button in my application during run time, it does not work. Furthermore, the code for for saving a record during run time does not work as well.

I have reinstall sql 2005 server and vb2005 express but of no use.

I have winxp with system of pentium 4.

What I should do to solve this dilemma.

Faisal.

You may want to check with this group of people:

http://www.tutorialized.com/tutorials/Visual-Basic/Database-Related/1

Buck Woody

In Stored Proc - How do i find the next key value (integer) and use it to populate a field

Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005.

What I want is that -

1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)

2). and set COMPANY_ID = (new) ID

Any thoughts?

Thanks

ALTER PROCEDURE usp_tbl_Company_Insert
@.Company_ID int,
@.Name varchar(200),

AS

<FIND THE NEW ID of the new row in the database>

@.Company_ID = (new ID)

INSERT INTO tbl_Company (Company_ID, Name,)
VALUES (@.Company_ID, @.Name)

If its an IDENTITY column then you cannot insert any value into the field. SQL Server will insert that value for you. If you want to find out the value that was inserted use SCOPE_IDENTITY() function immediately after the insert. You can also get the value into a variable and use it accordingly.

IN SQL Server Management Studio - Can connect to database sort of but Icon isn't green anymore

Preface: I'm a newbie at 2005 and sql server in general.

I can connect to a sql server (2005 express) but I can only make views and not run queries. When I open up a new query and create it with query designer and click add table I see no tables, views or anything in the box. When I create a new view in the database I do see tables and views though.

The little circle part of the icon on the server in the left hand pane is clear now when before it was green. I still can connect to the database ok but I can't use the query analyzer for queries anymore. I need to do that because I do have some slow running queries.

~Capt howdy

Select the database first (ie:AdventureWorks), 'new query' and then click design view.

|||I'm such a n00b with sql server it hurts sometimes. Thanks! I am used to access but it is definitely time to step up.

~Capt. Howdy

IN SQL Server Management Studio - Can connect to database sort of but Icon isn't green anym

Preface: I'm a newbie at 2005 and sql server in general.

I can connect to a sql server (2005 express) but I can only make views and not run queries. When I open up a new query and create it with query designer and click add table I see no tables, views or anything in the box. When I create a new view in the database I do see tables and views though.

The little circle part of the icon on the server in the left hand pane is clear now when before it was green. I still can connect to the database ok but I can't use the query analyzer for queries anymore. I need to do that because I do have some slow running queries.

~Capt howdy

Select the database first (ie:AdventureWorks), 'new query' and then click design view.

|||I'm such a n00b with sql server it hurts sometimes. Thanks! I am used to access but it is definitely time to step up.

~Capt. Howdy

In SQL Server 2005, is there any equivalent of taskpad of SQL Server 2000

In SQL Server 2005, is there any equivalent of taskpad of SQL Server 2000. (in Express Edition ) ?

There is no equivalent to the SQL Enterprise Manager 2000 task pads in Management Studio Express.

Sorry,
Steve

|||is that avilable on SQL Server Management Studio ?|||There are graphical reports available in SQL Server Management Studio. I don't believe these are completely comparable to the SQL Server 2000 task pads though.|||

How can I find out the amount of free space in a data / Log file ? (similar to SQL 2000 taskpad) ?

Any good reason why its been removed from SQL 2005 ?

|||

I found it.

From SQL Server 2005 (SSMS), select the database & goto Summary tab (F7).

Click on Report drop-down & select the report you want to see......

In SQL Server 2005, is there any equivalent of taskpad of SQL Server 2000

In SQL Server 2005, is there any equivalent of taskpad of SQL Server 2000. (in Express Edition ) ?

There is no equivalent to the SQL Enterprise Manager 2000 task pads in Management Studio Express.

Sorry,
Steve

|||is that avilable on SQL Server Management Studio ?|||There are graphical reports available in SQL Server Management Studio. I don't believe these are completely comparable to the SQL Server 2000 task pads though.|||

How can I find out the amount of free space in a data / Log file ? (similar to SQL 2000 taskpad) ?

Any good reason why its been removed from SQL 2005 ?

|||

I found it.

From SQL Server 2005 (SSMS), select the database & goto Summary tab (F7).

Click on Report drop-down & select the report you want to see......

In SQL Server 2005, is there any equivalent of taskpad of SQL Server 2000

In SQL Server 2005, is there any equivalent of taskpad of SQL Server 2000. (in Express Edition ) ?

There is no equivalent to the SQL Enterprise Manager 2000 task pads in Management Studio Express.

Sorry,
Steve

|||is that avilable on SQL Server Management Studio ?|||There are graphical reports available in SQL Server Management Studio. I don't believe these are completely comparable to the SQL Server 2000 task pads though.|||

How can I find out the amount of free space in a data / Log file ? (similar to SQL 2000 taskpad) ?

Any good reason why its been removed from SQL 2005 ?

|||

I found it.

From SQL Server 2005 (SSMS), select the database & goto Summary tab (F7).

Click on Report drop-down & select the report you want to see......