PropertyGrid and SMO objects

With Visual studio 2005 I've mapped my SMO objects and a new PropertyGrid control, everything works fine (a lot thanks to Microsoft VS 2005 team for this wonderfull and power control).

However I have two problems :

1. if I make some change in my PropertyGrid how to apply these changes to related object in my database ?

2. If my user dosn't have enough permission to change my DB objects how to turn PropertyGrid to ReadOnly mode ? ReadOnly property doesn't exist and set Enabled property to false doesn't work since the user cannot navigate through properties

Any help ?

Thank youYou'll need to add an Apply button (or some other control) on your form to give you an event for committing changes. For many SMO classes, you'll be able to commit changes by simply calling the Alter() method on the object that is being displayed in the PropertyGrid.

One way to get the read-only behavior you are looking for would be to create a wrapper class for the SMO class that only exposes property getters for the SMO class properties. If there is only a property getter, the PropertyGrid makes the corresponding cell read-only. So if the user has sufficient privileges, set the SelectedObject property to the SMO object directly, otherwise set the SelectedObject to your read-only wrapper around the SMO object.|||

better put button on propertygrid...

create your own pg and on New()

.......

Dim f As ToolStrip = Me.TOOLSTRIP

Dim n As New ToolStripButton

n.Text = "ADD"

AddHandler n.Click, AddressOf cADD

f.Items.Add(n)

Dim nD As New ToolStripButton

nD.Text = "Save"

AddHandler nD.Click, AddressOf cSave

f.Items.Add(nD)

PropertyGrid and SMO objects

With Visual studio 2005 I've mapped my SMO objects and a new PropertyGrid control, everything works fine (a lot thanks to Microsoft VS 2005 team for this wonderfull and power control).

However I have two problems :

1. if I make some change in my PropertyGrid how to apply these changes to related object in my database ?

2. If my user dosn't have enough permission to change my DB objects how to turn PropertyGrid to ReadOnly mode ? ReadOnly property doesn't exist and set Enabled property to false doesn't work since the user cannot navigate through properties

Any help ?

Thank you

You'll need to add an Apply button (or some other control) on your form to give you an event for committing changes. For many SMO classes, you'll be able to commit changes by simply calling the Alter() method on the object that is being displayed in the PropertyGrid.

One way to get the read-only behavior you are looking for would be to create a wrapper class for the SMO class that only exposes property getters for the SMO class properties. If there is only a property getter, the PropertyGrid makes the corresponding cell read-only. So if the user has sufficient privileges, set the SelectedObject property to the SMO object directly, otherwise set the SelectedObject to your read-only wrapper around the SMO object.|||

better put button on propertygrid...

create your own pg and on New()

.......

Dim f As ToolStrip = Me.TOOLSTRIP

Dim n As New ToolStripButton

n.Text = "ADD"

AddHandler n.Click, AddressOf cADD

f.Items.Add(n)

Dim nD As New ToolStripButton

nD.Text = "Save"

AddHandler nD.Click, AddressOf cSave

f.Items.Add(nD)

Property QuotedIdentifierStatus is not available for UDF.. Help?

Hi ;
I'm using the release version of SQL Server 2005 tools (Microsoft SQL
Server Management Studio 9.00.1399.00) and .NET Framework 2.0
(2.0.50727.42) on Windows XP SP2.
My desire was to use the new tools against my existing SQL 2000 servers
as well as newly installed SQL 2005 so as to only have one place to
work in and access to new tool features. This has worked great except
for one issue. When trying to modify or script some table-valued
functions under Management Studio I get the following error:
===================================
Property QuotedIdentifierStatus is not available for
UserDefinedFunction '[dbo].[fnFunction]'. This property may not exist
for this object, or may not be retrievable due to insufficient access
rights. (Microsoft.SqlServer.Smo)
===================================
I can edit/script the function in the 2000 tools (Enterprise Manager
and SQL Analyzer) without problem. I can edit some table-valued
functions in Management Studio but not others. I have tried dropping
the functions and re-creating them in both 2000 and 2005 tools - the
create works fine in both places, but it remains
un-modifiable/scriptable in the 2005 tools.
Anyone else run into this problem?
Additional info available for the error
===================================
Program Location:
at
Microsoft.SqlServer.Management.Smo.PropertyCollection.HandleNullValue(Int32
index)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional(String
propName)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional[T](String
propName, T defaultValue)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.CheckTextCorectness(
String
ddlText, Boolean enforceCreate, Boolean checkName, String[]
expectedObjectTypes, DdlTextParserHeaderInfo& headerInfo)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.CheckAndManipulateTe
xt(String
ddlText, String[] expectedObjectTypes, ScriptingOptions so, Boolean
forCreate)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.GetTextHeader(Boolea
n
forAlter)
at
Microsoft.SqlServer.Management.Smo.UserDefinedFunction.ScriptHeader(Boolean
forAlter)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.S
criptExistingTextObjectToWindow(IManaged
Connection
mc)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.I
nvokeTextObject(IManagedConnection
connection)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.I
nvoke()
===================================
Essa, M. Mughal
Software Developer
Toronto, CanadaCan you post a CREATE script for one of your problematic table-valued
functions?
Hope this helps.
Dan Guzman
SQL Server MVP
"Essa" <essamughal@.hotmail.com> wrote in message
news:532E0285-F705-4CD7-BAE2-79C18B372B24@.microsoft.com...
> Hi ;
> I'm using the release version of SQL Server 2005 tools (Microsoft SQL
> Server Management Studio 9.00.1399.00) and .NET Framework 2.0
> (2.0.50727.42) on Windows XP SP2.
> My desire was to use the new tools against my existing SQL 2000 servers
> as well as newly installed SQL 2005 so as to only have one place to
> work in and access to new tool features. This has worked great except
> for one issue. When trying to modify or script some table-valued
> functions under Management Studio I get the following error:
> ===================================
> Property QuotedIdentifierStatus is not available for
> UserDefinedFunction '[dbo].[fnFunction]'. This property may not exist
> for this object, or may not be retrievable due to insufficient access
> rights. (Microsoft.SqlServer.Smo)
> ===================================
> I can edit/script the function in the 2000 tools (Enterprise Manager
> and SQL Analyzer) without problem. I can edit some table-valued
> functions in Management Studio but not others. I have tried dropping
> the functions and re-creating them in both 2000 and 2005 tools - the
> create works fine in both places, but it remains
> un-modifiable/scriptable in the 2005 tools.
> Anyone else run into this problem?
> Additional info available for the error
> ===================================
> Program Location:
> at
> Microsoft.SqlServer.Management.Smo.PropertyCollection.HandleNullValue(Int3
2
> index)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional(Strin
g
> propName)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional[T](Strin
g
> propName, T defaultValue)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.CheckTextCorectnes
s(String
> ddlText, Boolean enforceCreate, Boolean checkName, String[]
> expectedObjectTypes, DdlTextParserHeaderInfo& headerInfo)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.CheckAndManipulate
Text(String
> ddlText, String[] expectedObjectTypes, ScriptingOptions so, Boolean
> forCreate)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.GetTextHeader(Bool
ean
> forAlter)
> at
> Microsoft.SqlServer.Management.Smo.UserDefinedFunction.ScriptHeader(Boolea
n
> forAlter)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem
. ScriptExistingTextObjectToWindow(IManage
dConnection
> mc)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem
.InvokeTextObject(IManagedConnection
> connection)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem
.Invoke()
> ===================================
> --
> Essa, M. Mughal
> Software Developer
> Toronto, Canada|||Essa (essamughal@.hotmail.com) writes:
> I'm using the release version of SQL Server 2005 tools (Microsoft SQL
> Server Management Studio 9.00.1399.00) and .NET Framework 2.0
> (2.0.50727.42) on Windows XP SP2.
> My desire was to use the new tools against my existing SQL 2000 servers
> as well as newly installed SQL 2005 so as to only have one place to
> work in and access to new tool features. This has worked great except
> for one issue. When trying to modify or script some table-valued
> functions under Management Studio I get the following error:
>===================================
> Property QuotedIdentifierStatus is not available for
> UserDefinedFunction '[dbo].[fnFunction]'. This property may not exist
> for this object, or may not be retrievable due to insufficient access
> rights. (Microsoft.SqlServer.Smo)
>===================================
> I can edit/script the function in the 2000 tools (Enterprise Manager
> and SQL Analyzer) without problem. I can edit some table-valued
> functions in Management Studio but not others. I have tried dropping
> the functions and re-creating them in both 2000 and 2005 tools - the
> create works fine in both places, but it remains
> un-modifiable/scriptable in the 2005 tools.
> Anyone else run into this problem?
Yes, this is a known bug, and it has been resolved as fixed. It only
affects connections to SQL 2000.
See also
http://lab.msdn.microsoft.com/produ...95-62d1fb31d0a4
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> Yes, this is a known bug, and it has been resolved as fixed. It only
> affects connections to SQL 2000.
I should clarify that this does not mean that the fix is publicly
available. I only wanted to convey that Microsoft are aware of the
issue, and the fix is to be expected to appear in SP1 for SQL 2005.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Cool beans. Glad there's a fix.
I've got a bunch of SQL 2000 servers I need to script the schema for,
and I ran into the same UDF scripting problem myself.
Hope SP1 will be available soon.
- john
Erland Sommarskog wrote:
> Erland Sommarskog (esquel@.sommarskog.se) writes:
> I should clarify that this does not mean that the fix is publicly
> available. I only wanted to convey that Microsoft are aware of the
> issue, and the fix is to be expected to appear in SP1 for SQL 2005.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Property QuotedIdentifierStatus is not available

I'm using the release version of SQL Server 2005 tools (Microsoft SQL
Server Management Studio 9.00.1399.00) and .NET Framework 2.0
(2.0.50727.42) on Windows XP SP2.
My desire was to use the new tools against my existing SQL 2000 servers
as well as newly installed SQL 2005 so as to only have one place to
work in and access to new tool features. This has worked great except
for one issue. When trying to modify or script some table-valued
functions under Management Studio I get the following error:
===================================
Property QuotedIdentifierStatus is not available for
UserDefinedFunction '[dbo].[fnFunction]'. This property may not exist
for this object, or may not be retrievable due to insufficient access
rights. (Microsoft.SqlServer.Smo)
===================================
I can edit/script the function in the 2000 tools (Enterprise Manager
and SQL Analyzer) without problem. I can edit some table-valued
functions in Management Studio but not others. I have tried dropping
the functions and re-creating them in both 2000 and 2005 tools - the
create works fine in both places, but it remains
un-modifiable/scriptable in the 2005 tools.
Anyone else run into this problem?

>R<
Additional info available for the error
===================================
Program Location:
at
Microsoft.SqlServer.Management.Smo.PropertyCollect ion.HandleNullValue(Int32
index)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ge tPropValueOptional(String
propName)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ge tPropValueOptional[T](String
propName, T defaultValue)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjec tBase.CheckTextCorectness(String
ddlText, Boolean enforceCreate, Boolean checkName, String[]
expectedObjectTypes, DdlTextParserHeaderInfo& headerInfo)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjec tBase.CheckAndManipulateText(String
ddlText, String[] expectedObjectTypes, ScriptingOptions so, Boolean
forCreate)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjec tBase.GetTextHeader(Boolean
forAlter)
at
Microsoft.SqlServer.Management.Smo.UserDefinedFunc tion.ScriptHeader(Boolean
forAlter)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VsDocumentMenuItem.ScriptExistingTextObjectT oWindow(IManagedConnection
mc)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VsDocumentMenuItem.InvokeTextObject(IManaged Connection
connection)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VsDocumentMenuItem.Invoke()
===================================
I have the same problem.
I have one UDF that I can edit in Management Studio. The only thing that
differs from the ones that I cant edit is that the editable function is an
Inline "Table-valued function". The other ones are "Multistatement
Table-valued" functions.
"Rajah" <rajahd@.zrgwortz.com> wrote in message
news:1131631336.774261.157340@.g49g2000cwa.googlegr oups.com...
> I'm using the release version of SQL Server 2005 tools (Microsoft SQL
> Server Management Studio 9.00.1399.00) and .NET Framework 2.0
> (2.0.50727.42) on Windows XP SP2.
> My desire was to use the new tools against my existing SQL 2000 servers
> as well as newly installed SQL 2005 so as to only have one place to
> work in and access to new tool features. This has worked great except
> for one issue. When trying to modify or script some table-valued
> functions under Management Studio I get the following error:
> ===================================
> Property QuotedIdentifierStatus is not available for
> UserDefinedFunction '[dbo].[fnFunction]'. This property may not exist
> for this object, or may not be retrievable due to insufficient access
> rights. (Microsoft.SqlServer.Smo)
> ===================================
> I can edit/script the function in the 2000 tools (Enterprise Manager
> and SQL Analyzer) without problem. I can edit some table-valued
> functions in Management Studio but not others. I have tried dropping
> the functions and re-creating them in both 2000 and 2005 tools - the
> create works fine in both places, but it remains
> un-modifiable/scriptable in the 2005 tools.
> Anyone else run into this problem?
>
> Additional info available for the error
> ===================================
> Program Location:
> at
> Microsoft.SqlServer.Management.Smo.PropertyCollect ion.HandleNullValue(Int32
> index)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ge tPropValueOptional(String
> propName)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ge tPropValueOptional[T](String
> propName, T defaultValue)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjec tBase.CheckTextCorectness(String
> ddlText, Boolean enforceCreate, Boolean checkName, String[]
> expectedObjectTypes, DdlTextParserHeaderInfo& headerInfo)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjec tBase.CheckAndManipulateText(String
> ddlText, String[] expectedObjectTypes, ScriptingOptions so, Boolean
> forCreate)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjec tBase.GetTextHeader(Boolean
> forAlter)
> at
> Microsoft.SqlServer.Management.Smo.UserDefinedFunc tion.ScriptHeader(Boolean
> forAlter)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VsDocumentMenuItem.ScriptExistingTextObjectT oWindow(IManagedConnection
> mc)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VsDocumentMenuItem.InvokeTextObject(IManaged Connection
> connection)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VsDocumentMenuItem.Invoke()
> ===================================
>

Property AnsiNullsStatus is not available for UserDefinedFunction

I created a table valued function in SQL Server 2000 SP3 using SQL 2005 Management Studio, but when I try to edit it (Right-Click, Modify or Script Function As...) I get the following error:

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

I can edit it in other tools (Sql Query Analyzer) just fine. I'm in the sysadmin role.

Versions:

Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Any thoughts?

Thanks in advance
Dennis

This defect has been fixed in Service Pack 2.

Thanks,
Steve

|||Thanks for the quick response. For future reference, is there a way I could have found this out myself. I thought I searched all the usual places.

thanks
-dennis
|||Do you mean SP2 for Sql 2005? That hasn't been relased, has it?|||

I've installed SP2 for SQL Server 2005 but I get the same error.

I'm using Express Edition and the SP2 Install reports

"The edition of product instance MSSQLSERVER does not qualify for this update" for MSSQLSERVER\Database Services.

Has anybody any other suggestions?

Martin Wills

Property AnsiNullsStatus is not available for UserDefinedFunction

I created a table valued function in SQL Server 2000 SP3 using SQL 2005 Management Studio, but when I try to edit it (Right-Click, Modify or Script Function As...) I get the following error:

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

I can edit it in other tools (Sql Query Analyzer) just fine. I'm in the sysadmin role.

Versions:

Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Any thoughts?

Thanks in advance
Dennis

This defect has been fixed in Service Pack 2.

Thanks,
Steve

|||Thanks for the quick response. For future reference, is there a way I could have found this out myself. I thought I searched all the usual places.

thanks
-dennis|||Do you mean SP2 for Sql 2005? That hasn't been relased, has it?|||

I've installed SP2 for SQL Server 2005 but I get the same error.

I'm using Express Edition and the SP2 Install reports

"The edition of product instance MSSQLSERVER does not qualify for this update" for MSSQLSERVER\Database Services.

Has anybody any other suggestions?

Martin Wills

sql

Property AnsiNullsStatus is not available for UserDefinedFunction

I created a table valued function in SQL Server 2000 SP3 using SQL 2005 Management Studio, but when I try to edit it (Right-Click, Modify or Script Function As...) I get the following error:

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

I can edit it in other tools (Sql Query Analyzer) just fine. I'm in the sysadmin role.

Versions:

Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Any thoughts?

Thanks in advance
Dennis

This defect has been fixed in Service Pack 2.

Thanks,
Steve

|||Thanks for the quick response. For future reference, is there a way I could have found this out myself. I thought I searched all the usual places.

thanks
-dennis|||Do you mean SP2 for Sql 2005? That hasn't been relased, has it?|||

I've installed SP2 for SQL Server 2005 but I get the same error.

I'm using Express Edition and the SP2 Install reports

"The edition of product instance MSSQLSERVER does not qualify for this update" for MSSQLSERVER\Database Services.

Has anybody any other suggestions?

Martin Wills

Promlem with loging in Managment Studio

hi

I'm going to use Enterprise edition

when I want to log in Managment Studio I face a window asking me for Server Name in addition to Server Type and authentication mode.

what should I write in Server Name section.I know the format is:

ComputerName\Instance Name

but what should I write in Instance Name?

thanks

Hello -

If you've installed SQL Server on the system with all the defaults, you don't have to type anything in the instance name. It's just the name of the server. If you installed SQL Server Express, it installs as a named instance by default. Usually it is named SQLExpress, so the name would be:

(your Server name)\SQLEXPRESS

To find out if the server is default or named, open a command prompt in Windows and type this:

NET START

Look for the service that starts with "SQL Server" in the name and to the right you'll see if it is a named instance or not. It will have the name of the instance in parenthesis, and if it is MSSQLSERVER it's default, and you don't have to type anything other than the server name. If it has something else there, it is named and that is the name you type.

More here: http://www.mssqltips.com/tip.asp?tip=1048

Note: If this answers your question, make sure you mark it "answered" so that others can find it!

Buck Woody
http://www.buckwoody.com

|||

You have to know what the instance name is; we can't tell you that. The best advice I can give you is to launch the SQL Server Configuration Manager (In your Start Menu->SQL Server 2005->Configuration Tools folder), browse to the Services tab, and locate any service that has a Service Type of "SQL Server", and then work from there.

If the service name is "SQL Server (MSSQLSERVER)" then you do not have an instance name (just use the computer name)

If the service name is "SQL Server (SQLEXPRESS)" then use ComputerName\SqlExpress

Check out my SQL Server 2005 video tutorials: http://www.learnsqlserver.com/

|||Where is it? I searched it and it does not come up in my search but when I go to microsoft .com they say it is already on the compuer

Promlem with loging in Managment Studio

hi

I'm going to use Enterprise edition

when I want to log in Managment Studio I face a window asking me for Server Name in addition to Server Type and authentication mode.

what should I write in Server Name section.I know the format is:

ComputerName\Instance Name

but what should I write in Instance Name?

thanks

Hello -

If you've installed SQL Server on the system with all the defaults, you don't have to type anything in the instance name. It's just the name of the server. If you installed SQL Server Express, it installs as a named instance by default. Usually it is named SQLExpress, so the name would be:

(your Server name)\SQLEXPRESS

To find out if the server is default or named, open a command prompt in Windows and type this:

NET START

Look for the service that starts with "SQL Server" in the name and to the right you'll see if it is a named instance or not. It will have the name of the instance in parenthesis, and if it is MSSQLSERVER it's default, and you don't have to type anything other than the server name. If it has something else there, it is named and that is the name you type.

More here: http://www.mssqltips.com/tip.asp?tip=1048

Note: If this answers your question, make sure you mark it "answered" so that others can find it!

Buck Woody
http://www.buckwoody.com

|||

You have to know what the instance name is; we can't tell you that. The best advice I can give you is to launch the SQL Server Configuration Manager (In your Start Menu->SQL Server 2005->Configuration Tools folder), browse to the Services tab, and locate any service that has a Service Type of "SQL Server", and then work from there.

If the service name is "SQL Server (MSSQLSERVER)" then you do not have an instance name (just use the computer name)

If the service name is "SQL Server (SQLEXPRESS)" then use ComputerName\SqlExpress

Check out my SQL Server 2005 video tutorials: http://www.learnsqlserver.com/

promblems connecting to db across a network

Hello,

I've built an application using c#, visual studio 2005 and sql server 2000. The database is sitting on another machine on the network.

When I test the application from visual studio, it all works fine - no problems connecting to the database etc.

However, If I publish the website and move it to the same server as the database and set up the site and users through IIS etc, I get the following error

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

my connection string is

<connectionStrings>

<addname="cashinsConnectionString"connectionString="Data Source=Londevs;Initial Catalog=cashins;User ID=Cash; Password=Cash123;"

providerName="System.Data.SqlClient" />

</connectionStrings>

Anyone have any ideas why this is happening?

Thanks

fogofogo:

Login failed for user 'NT AUTHORITY\NETWORK SERVICE

You are trying to connect to the database with the above account which doesn't have the necessary permissions to connect.

|||

Thanks for your reply.

So do I need to set these permissions on the database or on the server where the database is sitting?

|||

actually - this is strange. I've just put together a smaller application with the EXACT same connection string as above, and it worked fine.

how could that be??

|||

It's most likely a problem with your web.config file (possibly it's doing some impersonation or windows authentication) rather than just the connection string. Check both web.config files to see what you are doing differently.

|||

Thanks.

I compared them both using winmerge and they are both the same.

here my config file

12<!--3 Note: As an alternative to hand editing this file you can use the4 web admin tool to configure settings for your application. Use5 the Website->Asp.Net Configuration option in Visual Studio.6 A full list of settings and comments can be found in7 machine.config.comments usually located in8 \Windows\Microsoft.Net\Framework\v2.x\Config9-->10<configuration>11<appSettings/>12<connectionStrings>1314 <add name="cashinsConnectionString" connectionString="Data Source=servername;Initial Catalog=databasename;User ID=Cash; Password=Cash123;" providerName="System.Data.SqlClient"/>15 </connectionStrings>16 <system.web>17<!--18 Set compilation debug="true" to insert debugging19 symbols into the compiled page. Because this20 affects performance, set this value to true only21 during development.22 -->23<compilation debug="true"/>24<!--25 The <authentication> section enables configuration26 of the security authentication mode used by27 ASP.NET to identify an incoming user.28 -->29<authentication mode="Forms"/>3031<!--32 The <customErrors> section enables configuration33 of what to do if/when an unhandled error occurs34 during the execution of a request. Specifically,35 it enables developers to configure html error pages36 to be displayed in place of a error stack trace.3738 <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">39 <error statusCode="403" redirect="NoAccess.htm" />40 <error statusCode="404" redirect="FileNotFound.htm" />41 </customErrors>42 -->43</system.web>44</configuration>

all looks ok right?

I also pass the connection through...

1private static String connstring = ConfigurationManager.ConnectionStrings["cashinsConnectionString"].ConnectionString;2private static SqlConnection conn =new SqlConnection(connstring);

so I can access the connection from a .cs file that holds my classes and methods etc. Could that be the source of the problem?

Thanks

|||

Hi,

From your description, it seems that you application can connect to your database while lunched from Visual Studio but failed when lunched in IIS, right?

You can try the following steps to check if "AUTHORITY\NETWORK SERVICE" has been permitted to visit the database.

1. Open your Enterprise Manager and open the security node in your local sql server.
2. Click on "Logins", try to see if "AUTHORITY\NETWORK SERVICE" appeared in that list, if not, create the user and assign the corresponding server roles.

Thanks.

|||

Cool! thanks

Promblem with log in Managment studio

hi

Until now I was using SQLServer Express edition and whenever I wanted to log in Management Studio In the Server Name section I used to wirte MyComputername\SQLExpress

but now I'm going to use Enterprise edition

what should I write in the Server Name section to log in Management studio?

Hi,

well, that depends. If you are using a default instance it should be just the "servername" or "." or "(localhost)". If you are using a named instance it should be something like your mentioned combination with the name of the instance substituted with the SQLExpress keyword.

MachineName\NameInstanceName

HTH, Jens K. Suessmeyer.

http:://www.sqlserver2005.de

Projects in SQL Management Studio

I love the template explorer in SQL Management Studio 2005. However, why can't we save these files to a project we've started in Management Studio. I hope in the future that we will be able to change the folder names of projects in Management Studio, and that we will be able to drag template items from the template explorer to our project in the Solution Explorer window.

SQL Project team, can you please add this in the future?

Ron, thanks for your suggestion. I will forward on your comments to my team.

http://connect.microsoft.com is a customer portal where our users can file bugs, vote on their importance, and track their progress throughout our project's lifecycles. You'll be able to connect to the exact member of the team who owns any particular area of SQL Server. Would you mind submitting this idea there and posting a link back to the forum?

Thanks!

Paul A. Mestemaker II
Program Manager
SQL Server
http://blogs.msdn.com/sqlrem/

|||

I have posted this suggestiong and a few other project-related suggestions in a post on connect.microsoft.com. Here's the link:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=151213

Project Type - Business Intelligence

I have sql server 2005 beta 2 and Visual Studio .Net 2003 installed.
I do not have the project type "Business Intelligence" in Visual Studio.
I have tried everything I could think of.
it is supposed to be there correct?On Sat, 30 Oct 2004 09:33:01 -0700, "Bryan Farrell"
<BryanFarrell@.discussions.microsoft.com> wrote:
>I have sql server 2005 beta 2 and Visual Studio .Net 2003 installed.
>I do not have the project type "Business Intelligence" in Visual Studio.
>I have tried everything I could think of.
>it is supposed to be there correct?
Bryan,
Do you have the beta 2 of SQL Server 2005 or of SQL Server 2005
Express?
If you have Express then the Business Intelligence Development Studio
is not included.
If you have the beta 2 of SQL Server 2005 then you should be able to
find the Business Intelligence Development Studio in the SQL Server
2005 folder under All Programs.
Andrew Watt
MVP - InfoPath|||i have beta 2 of SQL Server 2005.
i have the Business Intelligence Development Studio.
but what i am looking for is Visual Studio .Net 2003 - then New Project -
then Business Intelligence. thats what i do no have. the Business
Intelligence project type.
thx.
"Andrew Watt [MVP - InfoPath]" wrote:
> On Sat, 30 Oct 2004 09:33:01 -0700, "Bryan Farrell"
> <BryanFarrell@.discussions.microsoft.com> wrote:
> >I have sql server 2005 beta 2 and Visual Studio .Net 2003 installed.
> >I do not have the project type "Business Intelligence" in Visual Studio.
> >I have tried everything I could think of.
> >
> >it is supposed to be there correct?
> Bryan,
> Do you have the beta 2 of SQL Server 2005 or of SQL Server 2005
> Express?
> If you have Express then the Business Intelligence Development Studio
> is not included.
> If you have the beta 2 of SQL Server 2005 then you should be able to
> find the Business Intelligence Development Studio in the SQL Server
> 2005 folder under All Programs.
> Andrew Watt
> MVP - InfoPath
>

Programming databaseconnections?

Hi!

I want some tutorials on how to actually program database applications. Everything seems so bound with the data controls provided by Visual Studio and coming from PHP I don't like to not have full control over my applications. So where can I learn about creating my own database applications without using repeater, gridview or formview?

Thank you in advance!

yes data controls can be over-welming and I see many instances where the pursuit of them consumes more effort that a more procedural approach. Coming from PHO, have you looked at http://www.learn2asp.net/php/Campaign.aspx - these should help your transition to ASP.NET from PHP.

There are also some good tutorials on programming database applications at http://www.asp.net/learn/videos/default.aspx?tabid=63

Programatically Verifying SQL Syntax

OK, so I'm working on a project that requires the dynamic generation of SQL statements. Now in Visual Studio .NET I'm sure many of you have used that wonderful visual database tool that allows you to magically "verify sql syntax" at the click of a button. It takes a query that looks like this:

"SELECT A.AFFID, A.UserName, A.Pass, A.FirstName, A.LastName, A.Company, A.Street, A.City, A.State, A.ZIP, A.Country, A.phone, P.EMail, A.RecieveEMail, A.SSN, A.JoinDate, A.AffType, P.ProductId, P.BuyerId FROM Affiliates A, Purchases P WHERE (ProductId > 0) ORDER BY 1"

and transforms it into something that looks like this:

"SELECT A.AFFID, A.UserName, A.Pass, A.FirstName, A.LastName, A.Company, A.Street, A.City, A.State, A.ZIP, A.Country, A.phone, P.Email, A.RecieveEMail, A.SSN, A.JoinDate, A.AffType, P.ProductID, P.BuyerID
FROM Affiliates A CROSS JOIN Purchases P
WHERE (P.ProductID > 0)
ORDER BY A.AFFID"

My question is: is there anything in the .NET platform that would allow me to achieve the same kind of result programatically (i.e. transform standard sql to tsql syntax and verify the syntax of the query at run time)?

Any help would be appreciated! Cheers.No, you have to sit down and start programming.

Having a parser generator available helps, if you find a SQL syntax as LALR.|||I was afraid you would say that.