ALTER SQL Server 2000 functions on SQL Express

I installed SQL Server Management Express and try to access to a SQL Server 2000 remotly.

I connected correctly but when I try to alter an existing function, I get the following error message:

TITLE: Microsoft SQL Server Management Studio Express

Property AnsiNullsStatus is not available for UserDefinedFunction '[dbo].[functionName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Express.Smo)

Note that functionName contain the correct function name.

Can someone confirm me that SQL Server Managmt Express can not alter function from previous SQL version or may I need to set some parameters on Server 2000.

Thanks

I'm moving this to the Tools forum.

Mike

Allowing remote connections during setup?

Is there a way to automatically setup SQL Server Express to allow remote connections during the installation process?

We are deploying SQL Server Express with our application and I really can't ask SMB customers to go through a series of rather complicated steps after our "turn key" setup installs everything in order for any of the other computers in their office to connect.

What's the reasoning behind that, anyway? Why create a database server setup which by default doesn't allow anyone except the server to access it? I guess that makes sense for ASP.NET but the web fad isn't the only platform developers use these days.

Any assistance would be greatly appreciated.

Cheers,

Evan

Evan, have you played around with the DISABLENETWORKPROTOCOLS switch? The default for Express is TCP=Off (1). This is the snippet from BOL.

;--
; The DISABLENETWORKPROTOCOLS switch is used to disable network protocol for SQL Server instance.
; Set DISABLENETWORKPROTOCOLS = 0; for Shared Memory= On, Named Pipe= On, TCP= On
; Set DISABLENETWORKPROTOCOLS = 1; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= Off
; Set DISABLENETWORKPROTOCOLS = 2; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= On

; Note: DISABLENETWORKPROTOCOLS if not specified has the following defaults.
; Default value for SQL Server Express/Evaluation/Developer: DISABLENETWORKPROTOCOLS =1
; Default value for Enterprise/Standard /Workgroup: DISABLENETWORKPROTOCOLS =2

Thanks,
Samuel Lester (MSFT)

allowing access to database only through stored procedures

I have read that it is possible to configure sql server express so that the database can only beaccessed through stored procedures. Can anyone tell me how to do this.

Many thanks.

martin

Really? Where is the original article which mentions this? I guess it actually talks about security management in SQL. Yes you can limit permissions of logins (or users after mapping the logins to databases). Image such a scenario: you want a login to do some specific updates to a table, but you do not want to grant UPDATE permission on the table to the login (if you do so, the login can do any UPDATE as he like on the table). In this case you can create a stored procedure to do the specific update, and then you give the EXECUTE permission to the login. This is one of the advantages of SPs.

Stored procedures offer numerous advantages. They can:

Share application logic with other applications, thereby ensuring consistent data access and modification.

Stored procedures can encapsulate business functionality. Business rules or policies encapsulated in stored procedures can be changed in a single location. All clients can use the same stored procedures to ensure consistent data access and modification.

|||

thank you for your reply

martin

Allow iis to connect to a sql express database

Folks,

I have an asp.net web app developed in visual studio 2005. The app works fine with the development server from visual studio and sql express. When I try to test the app under IIS before deploying to my host I get a permission not allowed message.

"[SqlException (0x80131904): User does not have permission to perform this action.]".

How do I give my web app access to my sql server database?

Thanks

Doug

These references should help:

Web Applications -Connect to SQL Server
Configuring an ASP.NET 2.0 Application to Work with Microsoft SQL Server 2000 or SQL Server 2005
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395
How To: Create a Service Account for an ASP.NET 2.0 Application
http://msdn2.microsoft.com/en-us/library/ms998297.aspx
How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0
http://msdn2.microsoft.com/en-us/library/ms998300.aspx

Allow iis to connect to a sql express database

Folks,

I have an asp.net web app developed in visual studio 2005. The app works fine with the development server from visual studio and sql express. When I try to test the app under IIS before deploying to my host I get a permission not allowed message.

"[SqlException (0x80131904): User does not have permission to perform this action.]".

How do I give my web app access to my sql server database?

Thanks

Doug

These references should help:

Web Applications -Connect to SQL Server
Configuring an ASP.NET 2.0 Application to Work with Microsoft SQL Server 2000 or SQL Server 2005
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395
How To: Create a Service Account for an ASP.NET 2.0 Application
http://msdn2.microsoft.com/en-us/library/ms998297.aspx
How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0
http://msdn2.microsoft.com/en-us/library/ms998300.aspx

All-of-a-sudden, I get "Invalid object name"

I have been developing a database in SQL Server Management Studio Express for the past few weeks. I have been writing stored procedures and functions, creating and deleting tables, etc., etc. and everything has been working just fine.
Today, however, if I try to run any of the functions I have written I get "Invalid object name 'functionName'."
This occurs even if I create a new function, execute the CREATE statement. Then if I refresh the Functions folder I can see my newly created function, but I cannot run it.
This is terribly frustrating.

Does anyone have any ideas as to what might have changed? I am logging in as the same user, on the same machine that I have always logged in on. I created the database that is giving the error.

When I right-click SQLEXPRESS in the object browser, and go to Permissions in the Server Properties dialog, no permissions are selected as "granted" for my login name under any of the Logins/Roles. (ex. BUILTIN\Administrators: my login name is listed in the "Explicit permissions for BUILTIN\Administrators" list, but no permissions are granted. If I go down the list selecting [checking] the various permissions, then close the dialog. When I open the dialog again, rows have been added to the list that have the Permission name, then "sa" listed as the Grantor, with that row checked. But, there are still no checks next to my login name.)

The only thing that has changed on my machine, that I am aware of, is that a Windows Authentication update ran on my machine earlier today. I am using Windows Authentication in my SQL Server instance. I assume it has something to do with that.

Please help. This is urgent. We have a presentation on this project in 3 days.
ugh.

Thanks in advance for any help you can provide.

hi,

I'd check that your database users' (database principal) default schema is still the one you are used to have too...

and obviously I'd verify all your code includes full object references in the form "schema_name.object_name" and not only

CREATE PROCEDURE usp_ProcName

....

EXEC usp_ProceureName

but

CREATE PROCEDURE the_schema.usp_ProcedureName

....

EXEC the_schema.usp_ProcedureName

and the like..

regards

All Hardware Being Equal

Does SQL Server 2005 Express perform equal to SQL Server 2005 Enterprise? Basically, if the hardware that both editions were deployed on was identical and configured with in conjunction with the hardware limitations that Express imposes, would the two editions perform the same when running the same querries?

I have read that SQL Express uses the same database engine but does this mean that the Express edition will perform as well as the Enterprise edition?

SQL Express has certain limits. Only 1 CPU is used, only 1GB of RAM is used, no 64-bit support, databases can't be larger than 4GB. If you're comparing SQL Express to SQL Enterprise on a hardware above these things, Enterprise will definitely be faster as it doesn't have such limits.

From a software standpoint, the only performance feature difference I'm aware of is that SQL Enterprise will consider Indexed Views when optimizing queries... any lesser version (inlcuding SQL Standard) will not do this.

There may be differences in the default configuration of the editions, too.

So, as you can see, it's a bit tricky to get an apples-to-apples comparison :-) All things considered, though, the performance should be pretty close on small databases.

-Ryan / Kardax