Adding a new column of a UDT type fails on "invalid data type"

Hi all,

A strange thing happened to me a few days ago - I tried to modify a table, using the SQL server 2005 management studio, in order to add a new column to an existing table. I've entered the column's name and selected one of my UDTs for the column type. When I pressed "enter", I got the following message "invalid data type" !

Needless to say that shouldn't have happen, didn't happen it the previous version (CTP) and for some reason it works on other computers having the same version !

BTW, the database is sql server 2000.

Any ideas what this works on other computers but not on mine ?

Should I re-install the SQL server ? or the framework ?

Thanks,

Ido.

I'm still getting the error - currently on 2 out of 3 computers.

Any ideas ?

Ido.

sql

Add-In Support For Management Studio

Is there add-in support for SQL Server 2005 Management Studio?

I want to build a script management add-in that is callable from the popup menu of the query window. I haven't been able to find anything on building add-ins for Management Studio on MSDN or the web. Did I miss something? Or is SDK/add-in support not available yet for Mangement Studio?

Thanks.

- Grant

Grant,

We do not current support third-party add-ins in Management Studio, though we don't explicitly lock this down. There are a few people out there who have figured out how get their add-in to surface. If you decide to go this route there is no guarantee that we won't break you in a service pack or future release.

Cheers,
Dan

add user with wiondows auth.. without using computer management

hi ALL

how can i add new user to the windows with out using management console

can i do that with api or any thing plz answer rapid

the task is i need to make magamant program to add the users from it in applcation that use sqlserver 2000

thanks

Probably the easiest way to do this is at the command line with the "net user" command:

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/net_user.mspx

You can also access the "System.DirectoryServices" API in C# to do this programmatically:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdirectoryservices.asp

Sung

Add role to Analysis Services Database

Does anyone know if there is a way to use a SSIS task to add a role to SSAS cube? At the moment I use Management Studio, Right Click "Roles" under the cube and go through New Role wizard, but I'd like to be able to include this in a SSIS package instead.

Thanks

Richard

You can do it through the Script task, employing the AMO (Analysis Management Objects) library. I recommend creating the role in SSMS first but instead of submitting the change to the system, clicking the Script button at the top of the New Role dialog box to view the XMLA\ASSL script it creates. The script can be a guide for your AMO development (as AMO is just a wrapper for the XMLA\ASSL languages).

Good luck,

Bryan

|||

Following along with what Bryan had mentioned you could script a role using Management Studio to use as a template then use and expression variable and an Analysis Services Execute DDL Task in SSIS to accomplish this as an ongoing solution. The Analysis Services Execute DDL Task will allow you to customize and execute the role creation/modification command and avoid having to use the script task.

Hope that helps!

|||

Thanks! Wow. Cool. Kind of got it to work but don't know quite how!

In SSMS scripting the role worked nicely and was able to run it in a query window and it did exactly what was needed.

I'm pretty useless at VB.NET so trying the Script Task in SSIS, I hit design script, added AMO as a reference and pasted the SSMS script where it says "Add your code here" inside of Public Sub Main(). Of course it doesn't work that simply. But is there a really simple answer to what I need or am I going to have to go off and learn some VB.NET ?

So then I tried Execute DDL task, and muddled my way around it. I save the original script as a .xmla file in the file system. Then in DDL task under DDL selected File Connection as SourceType and connected to this file. Executed task and it did what was needed.

I guess this is good enough as it works, but I wouldn't mind understanding better what I am doing!!

Thanks

Richard

|||

Here is a code sample from a past project where we created a role for each entry in a table. (Just sharing that last part so you understand the database query in the code.)

Code Snippet

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

Public Class ScriptMain

Public Sub Main()

Dim server As New Microsoft.AnalysisServices.Server

Try

' CONNECT TO THE SQL SERVER ANALYSIS SERVICES (SSAS) SERVER

server.Connect("localhost")

' THE SSAS DATABASE TO CONNECT TO

Dim database As New Microsoft.AnalysisServices.Database

database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

' GET A LIST OF ROLES TO CREATE

' Connection String comes from the 'METADATA' Connection Manager

Dim myConnection As New SqlConnection(Dts.Connections.Item(0).ConnectionString.ToString())

Dim myCommand As SqlCommand = New SqlCommand("SELECT ID FROM dbo.Roles (NOLOCK) WHERE ID > 0", myConnection)

myConnection.Open()

' FILL THE DATAREADER

Dim dr As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

' LOOP THROUGH THE DATAREADER AND BUILD A ROLE

If (dr.HasRows) Then

While (dr.Read())

CreateRole("Role for ID ", dr("ID").ToString, database)

End While

End If

' CLOSE THE DATAREADER

dr.Close()

' CLOSE THE SQL SERVER DB CONNECTION

myConnection.Close()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)

Finally

' DISCONNECT THE SSAS SERVER

server.Disconnect()

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

Private Sub CreateRole(ByVal rolePrefix As String, ByVal dsNumber As String, ByVal asDatabase As Database)

Try

' WILL CREATE A ROLE IN THE ASSIGNED DB WITH THE NAME AND KEY VALUE

Dim newRole As Role

' CREATE THE NEW ROLES NAME

newRole = asDatabase.Roles.Add(rolePrefix + " " + dsNumber)

newRole.Description = "Role for " + rolePrefix + " " + dsNumber

newRole.Update()

Catch ex As Exception

Throw New Exception(ex.Message.ToString())

End Try

End Sub

End Class

add domain login from management studio

We have a SQL Server 2005 SP2 server that also functions as a
secondary domain controller in active directory. Everything works
extremely well except for 1 bug that was found today. Currently
windows authentication is the only method. When creating a new sql
login either physically at the server or through a remote desktop
connection the user is found in the domain and works just fine.
However when trying to do the same process using management studio
from a desktop connecting to the sql instance as soon as you try to
find the user in the domain you get this error: "The program cannot
open the required dialog box because it cannot determine whether the
computer name "COMPNAME" is joined to a domain."
I have sysadmin privileges on the sql server and if I use my login
name on the server physically I am able to create logins.
Any help would be greatly appreciated.
Thanks
MattI've previously posted a bug to connect.microsoft.com about this. Please go
there
(https://connect.microsoft.com/SQLSe...=126
183) and let them know of your problem.
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"Matt Ziegler" wrote:

> We have a SQL Server 2005 SP2 server that also functions as a
> secondary domain controller in active directory. Everything works
> extremely well except for 1 bug that was found today. Currently
> windows authentication is the only method. When creating a new sql
> login either physically at the server or through a remote desktop
> connection the user is found in the domain and works just fine.
> However when trying to do the same process using management studio
> from a desktop connecting to the sql instance as soon as you try to
> find the user in the domain you get this error: "The program cannot
> open the required dialog box because it cannot determine whether the
> computer name "COMPNAME" is joined to a domain."
> I have sysadmin privileges on the sql server and if I use my login
> name on the server physically I am able to create logins.
> Any help would be greatly appreciated.
> Thanks
> Matt
>|||The problem that you are having is that when the SQL GUI opens the dialog to
select a user, it defaults to the local machine. Because the machine is
running as a domain controller it has no local accounts, and no local accoun
t
database.
Please go to connect.microsoft.com
(https://connect.microsoft.com/SQLSe...=126
183) and post additional information reguarding the issue.
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"Matt Ziegler" wrote:

> We have a SQL Server 2005 SP2 server that also functions as a
> secondary domain controller in active directory. Everything works
> extremely well except for 1 bug that was found today. Currently
> windows authentication is the only method. When creating a new sql
> login either physically at the server or through a remote desktop
> connection the user is found in the domain and works just fine.
> However when trying to do the same process using management studio
> from a desktop connecting to the sql instance as soon as you try to
> find the user in the domain you get this error: "The program cannot
> open the required dialog box because it cannot determine whether the
> computer name "COMPNAME" is joined to a domain."
> I have sysadmin privileges on the sql server and if I use my login
> name on the server physically I am able to create logins.
> Any help would be greatly appreciated.
> Thanks
> Matt
>

Ad: Problems Creating Instance in SQL 2005 management studio

Hello all,
Thanks in advance for any help you can provide.
I am having a problem creating a new NS instance using SQL 2005 management studio.
I am using the database element in my instance and application xml files like this,
in order to create the NS application in an existing database.
<Database>
<DatabaseName>%databasename%</DatabaseName>
<SchemaName>%schemaname%</SchemaName>
</Database>
the problem that I am having is that if I encounter an error somewhere in the process of creating this new instance the instance name and schema name I specified are still bound somewhere, and if I correct for the error and try to create the instance again, I receive a new error saying that there is already an instance with my instance name, or using the schema name.
If I try to list versions it tells me there are no instances, yet if I try to add a new instance it tells me there is already an instance using my instance name.
I have tried searching master database tables and views for anywhere where it might be stored, also tried poking around most of management studio and the registry, and just can not seem to find where it is remembering that I had tried to create this instance before.
It is like it is partially creating the instance, running into an error, and then not rolling back its changes somewhere.

Any help would be very much appreciated

-Derek-

Web Services Integration Manager

TempWorks Software

Si Vis Pacem, Para Bellum.

"Those who profess to favor freedom, and yet depreciate agitation, are men who want crops without plowing up the ground. They want rain without thunder and lightning. They want the ocean without the awful roar of its many waters. Power concedes nothing without demand. It never did and it never will." - Frederick Douglass

additional error information.
I tried to remove the database and restore from a backup, and yet it still believe the instance name is in use.
I notice it seems to be checking the NS InstanceMetaData for existing instances on this server...
"Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.CheckInstanceNameInUse"
anyone know where this metadata is ?

===================================

The specified name is used by another Notification Services instance on the server. Specify a different instance name.
Instance Name: TWNSinstance
Server Name: derekxps (Microsoft.SqlServer.NotificationServices)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=NSEventStrings&EvtID=DuplicateInstanceName&LinkId=20476

Program Location:

at Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.CheckInstanceNameInUse(SqlConnection sqlConnection, NSInstance instance)
at Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.Add(NSInstance instance)
at Microsoft.SqlServer.NotificationServices.Common.NSDatabaseCreator.Create(NSDBState dbState, SqlDatabaseAttributes databaseAttributes, List`1 createdStates)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.CreateDatabaseInfrastructure(Instance instance, NSSecureString argumentKey, NSInstance& nsInstance, Dictionary`2& nsApplications)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.Create(Instance instance, NSSecureString argumentKey, Boolean uiReporting)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.Create(NSSecureString sqlUserName, NSSecureString sqlPassword, String configFile, NSSecureString argumentKey, StringDictionary substitutions, Boolean uiReporting, String enforcedSqlServerSystem)
at Microsoft.SqlServer.Management.NotificationServicesUI.CreateInstanceGeneral.PerformCreate()|||

Hi Derek -

SQLNS 2005 stores its metadata in the MSDB database. Run this script in SSMS and see if your failed instance/application are listed.

USE MSDB

GO

SELECT*FROM NS90.NSInstanceInfo

SELECT*FROM NS90.NSApplicationInfo

HTH..

Joe

|||Thanks Joe,
I did find the instance names and schema names that it had "eaten" in there. cleared them and was able to install with the instance name I wanted.
Thanks for your help!

-Derek-

Ad: Problems Creating Instance in SQL 2005 management studio

Hello all,

Thanks in advance for any help you can provide.

I am having a problem creating a new NS instance using SQL 2005 management

studio.

I am using the database element in my instance and application xml files like

this,

in order to create the NS application in an existing database.

<Database>

<DatabaseName>%databasename%</DatabaseName>

<SchemaName>%schemaname%</SchemaName>

</Database>

the problem that I am having is that if I encounter an error somewhere in the

process of creating this new instance the instance name and schema name I

specified are still bound somewhere, and if I correct for the error and try to

create the instance again, I receive a new error saying that there is already

an instance with my instance name, or using the schema name.

If I try to list versions it tells me there are no instances, yet if I try to

add a new instance it tells me there is already an instance using my instance

name.

I have tried searching master database tables and views for anywhere

where it might be stored, also tried poking around most of management studio

and the registry, and just can not seem to find where it is remembering that I

had tried to create this instance before.

It is like it is partially creating the instance, running into an error, and

then not rolling back its changes somewhere.
Any help would be very much appreciated

-Derek-

Web Services

Integration Manager

TempWorks Software

Si Vis Pacem, Para Bellum.

"Those who profess to favor freedom, and yet depreciate agitation,

are men who want crops without plowing up the ground. They want rain without

thunder and lightning. They want the ocean without the awful roar of its

many waters. Power concedes nothing without demand. It never did and it

never will." - Frederick Douglass

additional error information.
I tried to remove the database and restore from a backup, and yet it still believe the instance name is in use.
I notice it seems to be checking the NS InstanceMetaData for existing instances on this server...
"Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.CheckInstanceNameInUse"
anyone know where this metadata is ?

===================================

The specified name is used by another Notification Services instance on the server. Specify a different instance name.
Instance Name: TWNSinstance
Server Name: derekxps (Microsoft.SqlServer.NotificationServices)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=NSEventStrings&EvtID=DuplicateInstanceName&LinkId=20476

Program Location:

at Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.CheckInstanceNameInUse(SqlConnection sqlConnection, NSInstance instance)
at Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.Add(NSInstance instance)
at Microsoft.SqlServer.NotificationServices.Common.NSDatabaseCreator.Create(NSDBState dbState, SqlDatabaseAttributes databaseAttributes, List`1 createdStates)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.CreateDatabaseInfrastructure(Instance instance, NSSecureString argumentKey, NSInstance& nsInstance, Dictionary`2& nsApplications)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.Create(Instance instance, NSSecureString argumentKey, Boolean uiReporting)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.Create(NSSecureString sqlUserName, NSSecureString sqlPassword, String configFile, NSSecureString argumentKey, StringDictionary substitutions, Boolean uiReporting, String enforcedSqlServerSystem)
at Microsoft.SqlServer.Management.NotificationServicesUI.CreateInstanceGeneral.PerformCreate()|||

Hi Derek -

SQLNS 2005 stores its metadata in the MSDB database. Run this script in SSMS and see if your failed instance/application are listed.

USE MSDB

GO

SELECT * FROM NS90.NSInstanceInfo

SELECT * FROM NS90.NSApplicationInfo

HTH..

Joe

|||Thanks Joe,
I did find the instance names and schema names that it had "eaten" in there. cleared them and was able to install with the instance name I wanted.
Thanks for your help!

-Derek-

Ad: Answer Worked!

Hello all,
Thanks in advance for any help you can provide.
I am having a problem creating a new NS instance using SQL 2005 management studio.
I am using the database element in my instance and application xml files like this,
in order to create the NS application in an existing database.
<Database>
<DatabaseName>%databasename%</DatabaseName>
<SchemaName>%schemaname%</SchemaName>
</Database>
the problem that I am having is that if I encounter an error somewhere in the process of creating this new instance the instance name and schema name I specified are still bound somewhere, and if I correct for the error and try to create the instance again, I receive a new error saying that there is already an instance with my instance name, or using the schema name.
If I try to list versions it tells me there are no instances, yet if I try to add a new instance it tells me there is already an instance using my instance name.
I have tried searching master database tables and views for anywhere where it might be stored, also tried poking around most of management studio and the registry, and just can not seem to find where it is remembering that I had tried to create this instance before.
It is like it is partially creating the instance, running into an error, and then not rolling back its changes somewhere.

Any help would be very much appreciated

-Derek-

Web Services Integration Manager

TempWorks Software

Si Vis Pacem, Para Bellum.

"Those who profess to favor freedom, and yet depreciate agitation, are men who want crops without plowing up the ground. They want rain without thunder and lightning. They want the ocean without the awful roar of its many waters. Power concedes nothing without demand. It never did and it never will." - Frederick Douglass

additional error information.
I tried to remove the database and restore from a backup, and yet it still believe the instance name is in use.
I notice it seems to be checking the NS InstanceMetaData for existing instances on this server...
"Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.CheckInstanceNameInUse"
anyone know where this metadata is ?

===================================

The specified name is used by another Notification Services instance on the server. Specify a different instance name.
Instance Name: TWNSinstance
Server Name: derekxps (Microsoft.SqlServer.NotificationServices)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=NSEventStrings&EvtID=DuplicateInstanceName&LinkId=20476

Program Location:

at Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.CheckInstanceNameInUse(SqlConnection sqlConnection, NSInstance instance)
at Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.Add(NSInstance instance)
at Microsoft.SqlServer.NotificationServices.Common.NSDatabaseCreator.Create(NSDBState dbState, SqlDatabaseAttributes databaseAttributes, List`1 createdStates)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.CreateDatabaseInfrastructure(Instance instance, NSSecureString argumentKey, NSInstance& nsInstance, Dictionary`2& nsApplications)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.Create(Instance instance, NSSecureString argumentKey, Boolean uiReporting)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.Create(NSSecureString sqlUserName, NSSecureString sqlPassword, String configFile, NSSecureString argumentKey, StringDictionary substitutions, Boolean uiReporting, String enforcedSqlServerSystem)
at Microsoft.SqlServer.Management.NotificationServicesUI.CreateInstanceGeneral.PerformCreate()|||

Hi Derek -

SQLNS 2005 stores its metadata in the MSDB database. Run this script in SSMS and see if your failed instance/application are listed.

USE MSDB

GO

SELECT*FROM NS90.NSInstanceInfo

SELECT*FROM NS90.NSApplicationInfo

HTH..

Joe

|||Thanks Joe,
I did find the instance names and schema names that it had "eaten" in there. cleared them and was able to install with the instance name I wanted.
Thanks for your help!

-Derek-