Load Multiple Signed Assemblies

I am trying to load multiple strongly named assemblies into the same database which are signed with the same .snk file (signed in Visual Studio). I use the following code to create an asymmetric key and login as Books Online recommends:

CREATE ASYMMETRIC KEY SQLCLRKey FROM FILE = 'D:\dba\bin\Assembly.dll'

CREATE LOGIN CLRAssembler FROM ASYMMETRIC KEY SQLCLRKey

GRANT UNSAFE ASSEMBLY TO CLRAssembler

GRANT EXTERNAL ACCESS ASSEMBLY TO CLRAssembler

REVOKE CONNECT SQL FROM CLRAssembler

Do I need to create a new login and asymmetric key for each assembly I load? If so, do I need to sign each with a different key because its giving me an error message when I try to create 2 separate asymmetric keys/logins from 2 different assemblies which have been signed with the same .snk file.

The only way I've gotten everything to load properly is to create a separate key for each assembly and sign each, then create separate logins and asymmetric keys in the database.

Is this the only way to do this? Or am I missing something?

First of all I think you mean:

CREATE ASYMMETRIC KEY SQLCLRKey FROM EXECUTABLE FILE = 'D:\dba\bin\Assembly.dll'

FROM FILE = '...' requires a file that has both the public and private key in it, but an assembly has only the public key in it. Also you should be creating this key in the master database.

In order to use an asymmetic key to enable an assembly to be loaded the asymmetric key must be the master database and include public key, but the private key is not required. When FROM EXECUTABLE FILE = '...' is used the only the public key for the asymmetric key is saved. This key can be used to create a login to grant usafe assembly to. Then, assuming the use has the other appropriate permissions, any assembly signed with this key can be loaded with permission_set = unsafe. A single login is used to load all of the assemblies that are signed with the same key... you can't load the same asymmetric key more than once in the same database. You will have to be sure that Visual Studio is signing all your assemblies with the same key. If you are having to create a new login for each assembly it sounds like Visual Studio is creating a new key for each of these assemblies. When you go to the properties for your visual studio project browse for a common key, don't create a new one.

You can create the asymmetric key directly from the snk file that visual studio creates, for example if myKey.snk is the key pair created by visual studio then:

USE master
GO

CREATE ASYMMETRIC KEY [MyAssemblyKey] FROM FILE = 'c:\keys\myKey.snk'
-- remove the private key, no reason to leave it hanging around.
ALTER ASYMMETRIC KEY [MyAssemblyKey] REMOVE PRIVATE KEY

CREATE LOGIN [LoginMyAssemblyKey] FROM ASYMMETRIC KEY [Key MyAssemblyKey]
GRANT EXTERNAL ACCESS ASSEMBLY TO [LoginMyAssemblyKey]

GO

Once you have done this any assemblies signed with myKey.snk can be deployed from visual studio with unsafe permission set.

Dan

Dan

|||

My mistake. I did mean EXECUTABLE FILE.

I started out trying to sign them all with the same key and then loading them individually and dropping the key and login, however this was producing an error (which I can post once I get back into the office).

Do I need to load them in the same batch or script if I want to use the same login? Because I was running them separately.

If not, how do I specify the login to use? I tried using the AUTHORIZATION command with it and it threw a permissions error.

|||

I'm not sure what you mean when you say you drop the login after creating the assembly.

If you drop the login, or take away the login's USAFE ASSEMBLY permission, you will not be able to use the assembly even though even though it has been created. The login created with the assemblies key is required whenever any function from the assembly is used.

Dan

|||

You need only use CREATE ASSEMBLY. Authorization is used to specify an owner, it is not related to whether or not the assembly can be external acess or unsafe. If the assembly is being created WITH EXTERNAL_ACCESS or UNSAFE, SQL Server will use the key inside of the assembly to find the login created with that key, then check the permissions granted to that login. It, in effect, does this whenever a function from that assembly is used too.

Dan

Listing and Changing Filegroup assignments in SSMS

Using SQL 2005 Server Management Studio how can I:
1. List the Filegroup being used by each Table (or vice versa)?
2. Move a Table to a different Filegroup?
Note on #2: My understanding is that if I change the storage location of a
clustered index then the table should move with it. But if I try to change
the Filegroup from Index Properties -> Storage tab I get an Error 1779
("Recreate failed for index 'pk'. ... Table already has a primary key defined
on it. Could not create constraint.")Hi Dave
"Dave Booker" wrote:
> Using SQL 2005 Server Management Studio how can I:
> 1. List the Filegroup being used by each Table (or vice versa)?
sp_help 'table' will give the data file location or you can query sys.tables
and sys.filegroups (see below)
> 2. Move a Table to a different Filegroup?
> Note on #2: My understanding is that if I change the storage location of a
> clustered index then the table should move with it. But if I try to change
> the Filegroup from Index Properties -> Storage tab I get an Error 1779
> ("Recreate failed for index 'pk'. ... Table already has a primary key defined
> on it. Could not create constraint.")
You would need to drop the PK or Clustered index first. Moving the
PK/clustered index will not move where the text is e.g.
CREATE DATABASE MyDB
ON PRIMARY
( NAME = MyDb_dat,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_Data1.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15 ),
FILEGROUP Secondary
( NAME = MyDB_dat2,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_Data2.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15 )
LOG ON
( NAME = Mydb_log,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_log.log',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
USe MyDb
GO
CREATE TABLE MyTab ( id int not null constraint PK_MyTable PRIMARY KEY,
col2 varchar(300),
txt text )
ON [PRIMARY]
TEXTIMAGE_ON [SECONDARY]
GO
EXEC sp_help MyTab
CREATE TABLE MyTab2 ( id int not null ,
col2 varchar(300),
txt text )
ON [PRIMARY]
GO
EXEC sp_help MyTab2
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
ALTER TABLE MyTab DROP CONSTRAINT PK_MyTable
GO
EXEC sp_help MyTab
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
ALTER TABLE MyTab ADD CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (id)
ON [SECONDARY]
GO
EXEC sp_help MyTab
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
CREATE CLUSTERED INDEX Ind_MyTab2 ON MyTab2 (ID)
ON [SECONDARY]
GO
EXEC sp_help MyTab2
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
To move text columns you would need to create a new table and suck the data
out of the original table
If you have a FK referencing your PK or UNIQUE CLUSTERED index then the FK
would have to be dropped first and re-created after you PK/Index has been
re-created.
John

Listing and Changing Filegroup assignments in SSMS

Using SQL 2005 Server Management Studio how can I:
1. List the Filegroup being used by each Table (or vice versa)?
2. Move a Table to a different Filegroup?
Note on #2: My understanding is that if I change the storage location of a
clustered index then the table should move with it. But if I try to change
the Filegroup from Index Properties -> Storage tab I get an Error 1779
("Recreate failed for index 'pk'. ... Table already has a primary key defined
on it. Could not create constraint.")
Hi Dave
"Dave Booker" wrote:

> Using SQL 2005 Server Management Studio how can I:
> 1. List the Filegroup being used by each Table (or vice versa)?
sp_help 'table' will give the data file location or you can query sys.tables
and sys.filegroups (see below)

> 2. Move a Table to a different Filegroup?
> Note on #2: My understanding is that if I change the storage location of a
> clustered index then the table should move with it. But if I try to change
> the Filegroup from Index Properties -> Storage tab I get an Error 1779
> ("Recreate failed for index 'pk'. ... Table already has a primary key defined
> on it. Could not create constraint.")
You would need to drop the PK or Clustered index first. Moving the
PK/clustered index will not move where the text is e.g.
CREATE DATABASE MyDB
ON PRIMARY
( NAME = MyDb_dat,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_Data1.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15 ),
FILEGROUP Secondary
( NAME = MyDB_dat2,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_Data2.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15 )
LOG ON
( NAME = Mydb_log,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_log.log',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
USe MyDb
GO
CREATE TABLE MyTab ( id int not null constraint PK_MyTable PRIMARY KEY,
col2 varchar(300),
txt text )
ON [PRIMARY]
TEXTIMAGE_ON [SECONDARY]
GO
EXEC sp_help MyTab
CREATE TABLE MyTab2 ( id int not null ,
col2 varchar(300),
txt text )
ON [PRIMARY]
GO
EXEC sp_help MyTab2
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
ALTER TABLE MyTab DROP CONSTRAINT PK_MyTable
GO
EXEC sp_help MyTab
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
ALTER TABLE MyTab ADD CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (id)
ON [SECONDARY]
GO
EXEC sp_help MyTab
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
CREATE CLUSTERED INDEX Ind_MyTab2 ON MyTab2 (ID)
ON [SECONDARY]
GO
EXEC sp_help MyTab2
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
To move text columns you would need to create a new table and suck the data
out of the original table
If you have a FK referencing your PK or UNIQUE CLUSTERED index then the FK
would have to be dropped first and re-created after you PK/Index has been
re-created.
John

Listing and Changing Filegroup assignments in SSMS

Using SQL 2005 Server Management Studio how can I:
1. List the Filegroup being used by each Table (or vice versa)?
2. Move a Table to a different Filegroup?
Note on #2: My understanding is that if I change the storage location of a
clustered index then the table should move with it. But if I try to change
the Filegroup from Index Properties -> Storage tab I get an Error 1779
("Recreate failed for index 'pk'. ... Table already has a primary key define
d
on it. Could not create constraint.")Hi Dave
"Dave Booker" wrote:

> Using SQL 2005 Server Management Studio how can I:
> 1. List the Filegroup being used by each Table (or vice versa)?
sp_help 'table' will give the data file location or you can query sys.tables
and sys.filegroups (see below)

> 2. Move a Table to a different Filegroup?
> Note on #2: My understanding is that if I change the storage location of a
> clustered index then the table should move with it. But if I try to chang
e
> the Filegroup from Index Properties -> Storage tab I get an Error 1779
> ("Recreate failed for index 'pk'. ... Table already has a primary key defi
ned
> on it. Could not create constraint.")
You would need to drop the PK or Clustered index first. Moving the
PK/clustered index will not move where the text is e.g.
CREATE DATABASE MyDB
ON PRIMARY
( NAME = MyDb_dat,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_Data1.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15 ),
FILEGROUP Secondary
( NAME = MyDB_dat2,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_Data2.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15 )
LOG ON
( NAME = Mydb_log,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_log.log',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
USe MyDb
GO
CREATE TABLE MyTab ( id int not null constraint PK_MyTable PRIMARY KEY,
col2 varchar(300),
txt text )
ON [PRIMARY]
TEXTIMAGE_ON [SECONDARY]
GO
EXEC sp_help MyTab
CREATE TABLE MyTab2 ( id int not null ,
col2 varchar(300),
txt text )
ON [PRIMARY]
GO
EXEC sp_help MyTab2
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
ALTER TABLE MyTab DROP CONSTRAINT PK_MyTable
GO
EXEC sp_help MyTab
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
ALTER TABLE MyTab ADD CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (id)
ON [SECONDARY]
GO
EXEC sp_help MyTab
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
CREATE CLUSTERED INDEX Ind_MyTab2 ON MyTab2 (ID)
ON [SECONDARY]
GO
EXEC sp_help MyTab2
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
To move text columns you would need to create a new table and suck the data
out of the original table
If you have a FK referencing your PK or UNIQUE CLUSTERED index then the FK
would have to be dropped first and re-created after you PK/Index has been
re-created.
John

List of server connections

Hello,

I use SQL Server Management Studio Express. When I want to connect to some server the application form offers me several servers that I used to connect to in past. Where can I manage this offer (delete some obsolete servers).

Thanks

hi,

I do think you find it in

C:\Documents and Settings\YourName\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

.. but, if it is, my advice is not to hack it..

regards

List of issues in Management Studio

I have been using Management Studio for a few days and it's full of
annoyannces.
1- I just tried to copy 200 rows from a table from Management Studio to
another similar table with a column that doesn't allow nulls. So I get the
same error popup message for each row and I can't cancel from this batch.
2- Why can't I multiselect tables and Stored procs to delete like I used to
in 2000's Enterprise Manager.
3- When I script a stored procedure in 2000's Enterprise Manager, I woule
the create, drop and permission statements ALL in one script. This way I
was able to recreate the SP in one shot even if it exited. Not in
Management Studio. The drop and create are two seperate functions. If you
wanted to script the grant permission, you have to do it from the database
level and go through a bunch of clicks just to do it for one SP. TIME
CONSUMIUNG!
4- So I decided to script the grant permissions from the database level
using the drop procedure and tell it to include the object-level
permissions. The generated script included the drop statements but no
grant statement. I guess it figured that if you're dropping the SP's, why
include include the grant statements? My idea was that the drop statements
are in one line so then I can delete them pretty easy. It didn't work.
5- As mentioned in my previous message, the import export wizard stops too
often and gives cryptic messages without indicating why it stopped in clear
English. That's why I resorted to copy and paste ( see 1)
6- I don't think the import/export wizard is smart enough to transfer the
child tables first to avoid the foreign key issues. This was a problem in
2000
7- When the transferring many tables which have identity columns, I had to
check the "Enable identity insert' checkbox and select 'delete rows in
destination table'. Is there a way to select this option for all tables
instead of doing it one table at a time?
8- Choosing 'modify' for a table and opening a table give the same exact
icon and text in the list of opened windows. There's no visual clue when
trying to select either.
9- No 'back' button in the last step of the import export wizard if the
wizard ran successfuly. This means if I want to select other tables, I have
to restart the wizard. See 10 below for why it's annoying.
10- The wizard doesn't remember my settings. I constantly transfer data
between two sql servers, the first uses windows authentication (local
server) and the other uses sql server authentication and the wizard always
defaults to windows authenitcation for both. I wish there's an option to
remember type of authentication, my username and password or at least
remember the type of authentication and username.
I hope these issues will be addressed in the next version.
John Dalberg
If you want to make MS aware of issues with their products, use the Product Feedback web-site at
http://lab.msdn.microsoft.com/productfeedback/.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Dalberg" <nospam@.nospam.sss> wrote in message news:20060329013741.245$vZ@.newsreader.com...
>I have been using Management Studio for a few days and it's full of
> annoyannces.
> 1- I just tried to copy 200 rows from a table from Management Studio to
> another similar table with a column that doesn't allow nulls. So I get the
> same error popup message for each row and I can't cancel from this batch.
> 2- Why can't I multiselect tables and Stored procs to delete like I used to
> in 2000's Enterprise Manager.
> 3- When I script a stored procedure in 2000's Enterprise Manager, I woule
> the create, drop and permission statements ALL in one script. This way I
> was able to recreate the SP in one shot even if it exited. Not in
> Management Studio. The drop and create are two seperate functions. If you
> wanted to script the grant permission, you have to do it from the database
> level and go through a bunch of clicks just to do it for one SP. TIME
> CONSUMIUNG!
> 4- So I decided to script the grant permissions from the database level
> using the drop procedure and tell it to include the object-level
> permissions. The generated script included the drop statements but no
> grant statement. I guess it figured that if you're dropping the SP's, why
> include include the grant statements? My idea was that the drop statements
> are in one line so then I can delete them pretty easy. It didn't work.
> 5- As mentioned in my previous message, the import export wizard stops too
> often and gives cryptic messages without indicating why it stopped in clear
> English. That's why I resorted to copy and paste ( see 1)
> 6- I don't think the import/export wizard is smart enough to transfer the
> child tables first to avoid the foreign key issues. This was a problem in
> 2000
> 7- When the transferring many tables which have identity columns, I had to
> check the "Enable identity insert' checkbox and select 'delete rows in
> destination table'. Is there a way to select this option for all tables
> instead of doing it one table at a time?
> 8- Choosing 'modify' for a table and opening a table give the same exact
> icon and text in the list of opened windows. There's no visual clue when
> trying to select either.
> 9- No 'back' button in the last step of the import export wizard if the
> wizard ran successfuly. This means if I want to select other tables, I have
> to restart the wizard. See 10 below for why it's annoying.
> 10- The wizard doesn't remember my settings. I constantly transfer data
> between two sql servers, the first uses windows authentication (local
> server) and the other uses sql server authentication and the wizard always
> defaults to windows authenitcation for both. I wish there's an option to
> remember type of authentication, my username and password or at least
> remember the type of authentication and username.
> I hope these issues will be addressed in the next version.
> John Dalberg

List of issues in Management Studio

I have been using Management Studio for a few days and it's full of
annoyannces.
1- I just tried to copy 200 rows from a table from Management Studio to
another similar table with a column that doesn't allow nulls. So I get the
same error popup message for each row and I can't cancel from this batch.
2- Why can't I multiselect tables and Stored procs to delete like I used to
in 2000's Enterprise Manager.
3- When I script a stored procedure in 2000's Enterprise Manager, I woule
the create, drop and permission statements ALL in one script. This way I
was able to recreate the SP in one shot even if it exited. Not in
Management Studio. The drop and create are two seperate functions. If you
wanted to script the grant permission, you have to do it from the database
level and go through a bunch of clicks just to do it for one SP. TIME
CONSUMIUNG!
4- So I decided to script the grant permissions from the database level
using the drop procedure and tell it to include the object-level
permissions. The generated script included the drop statements but no
grant statement. I guess it figured that if you're dropping the SP's, why
include include the grant statements' My idea was that the drop statements
are in one line so then I can delete them pretty easy. It didn't work.
5- As mentioned in my previous message, the import export wizard stops too
often and gives cryptic messages without indicating why it stopped in clear
English. That's why I resorted to copy and paste ( see 1)
6- I don't think the import/export wizard is smart enough to transfer the
child tables first to avoid the foreign key issues. This was a problem in
2000
7- When the transferring many tables which have identity columns, I had to
check the "Enable identity insert' checkbox and select 'delete rows in
destination table'. Is there a way to select this option for all tables
instead of doing it one table at a time?
8- Choosing 'modify' for a table and opening a table give the same exact
icon and text in the list of opened windows. There's no visual clue when
trying to select either.
9- No 'back' button in the last step of the import export wizard if the
wizard ran successfuly. This means if I want to select other tables, I have
to restart the wizard. See 10 below for why it's annoying.
10- The wizard doesn't remember my settings. I constantly transfer data
between two sql servers, the first uses windows authentication (local
server) and the other uses sql server authentication and the wizard always
defaults to windows authenitcation for both. I wish there's an option to
remember type of authentication, my username and password or at least
remember the type of authentication and username.
I hope these issues will be addressed in the next version.
John DalbergIf you want to make MS aware of issues with their products, use the Product
Feedback web-site at
http://lab.msdn.microsoft.com/productfeedback/.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Dalberg" <nospam@.nospam.sss> wrote in message news:20060329013741.245$vZ@.newsreader.co
m...
>I have been using Management Studio for a few days and it's full of
> annoyannces.
> 1- I just tried to copy 200 rows from a table from Management Studio to
> another similar table with a column that doesn't allow nulls. So I get the
> same error popup message for each row and I can't cancel from this batch.
> 2- Why can't I multiselect tables and Stored procs to delete like I used t
o
> in 2000's Enterprise Manager.
> 3- When I script a stored procedure in 2000's Enterprise Manager, I woule
> the create, drop and permission statements ALL in one script. This way I
> was able to recreate the SP in one shot even if it exited. Not in
> Management Studio. The drop and create are two seperate functions. If you
> wanted to script the grant permission, you have to do it from the database
> level and go through a bunch of clicks just to do it for one SP. TIME
> CONSUMIUNG!
> 4- So I decided to script the grant permissions from the database level
> using the drop procedure and tell it to include the object-level
> permissions. The generated script included the drop statements but no
> grant statement. I guess it figured that if you're dropping the SP's, why
> include include the grant statements' My idea was that the drop statement
s
> are in one line so then I can delete them pretty easy. It didn't work.
> 5- As mentioned in my previous message, the import export wizard stops too
> often and gives cryptic messages without indicating why it stopped in clea
r
> English. That's why I resorted to copy and paste ( see 1)
> 6- I don't think the import/export wizard is smart enough to transfer the
> child tables first to avoid the foreign key issues. This was a problem in
> 2000
> 7- When the transferring many tables which have identity columns, I had to
> check the "Enable identity insert' checkbox and select 'delete rows in
> destination table'. Is there a way to select this option for all tables
> instead of doing it one table at a time?
> 8- Choosing 'modify' for a table and opening a table give the same exact
> icon and text in the list of opened windows. There's no visual clue when
> trying to select either.
> 9- No 'back' button in the last step of the import export wizard if the
> wizard ran successfuly. This means if I want to select other tables, I hav
e
> to restart the wizard. See 10 below for why it's annoying.
> 10- The wizard doesn't remember my settings. I constantly transfer data
> between two sql servers, the first uses windows authentication (local
> server) and the other uses sql server authentication and the wizard always
> defaults to windows authenitcation for both. I wish there's an option to
> remember type of authentication, my username and password or at least
> remember the type of authentication and username.
> I hope these issues will be addressed in the next version.
> John Dalberg

List of issues in Management Studio

I have been using Management Studio for a few days and it's full of
annoyannces.
1- I just tried to copy 200 rows from a table from Management Studio to
another similar table with a column that doesn't allow nulls. So I get the
same error popup message for each row and I can't cancel from this batch.
2- Why can't I multiselect tables and Stored procs to delete like I used to
in 2000's Enterprise Manager.
3- When I script a stored procedure in 2000's Enterprise Manager, I woule
the create, drop and permission statements ALL in one script. This way I
was able to recreate the SP in one shot even if it exited. Not in
Management Studio. The drop and create are two seperate functions. If you
wanted to script the grant permission, you have to do it from the database
level and go through a bunch of clicks just to do it for one SP. TIME
CONSUMIUNG!
4- So I decided to script the grant permissions from the database level
using the drop procedure and tell it to include the object-level
permissions. The generated script included the drop statements but no
grant statement. I guess it figured that if you're dropping the SP's, why
include include the grant statements' My idea was that the drop statements
are in one line so then I can delete them pretty easy. It didn't work.
5- As mentioned in my previous message, the import export wizard stops too
often and gives cryptic messages without indicating why it stopped in clear
English. That's why I resorted to copy and paste ( see 1)
6- I don't think the import/export wizard is smart enough to transfer the
child tables first to avoid the foreign key issues. This was a problem in
2000
7- When the transferring many tables which have identity columns, I had to
check the "Enable identity insert' checkbox and select 'delete rows in
destination table'. Is there a way to select this option for all tables
instead of doing it one table at a time?
8- Choosing 'modify' for a table and opening a table give the same exact
icon and text in the list of opened windows. There's no visual clue when
trying to select either.
9- No 'back' button in the last step of the import export wizard if the
wizard ran successfuly. This means if I want to select other tables, I have
to restart the wizard. See 10 below for why it's annoying.
10- The wizard doesn't remember my settings. I constantly transfer data
between two sql servers, the first uses windows authentication (local
server) and the other uses sql server authentication and the wizard always
defaults to windows authenitcation for both. I wish there's an option to
remember type of authentication, my username and password or at least
remember the type of authentication and username.
I hope these issues will be addressed in the next version.
John DalbergIf you want to make MS aware of issues with their products, use the Product Feedback web-site at
http://lab.msdn.microsoft.com/productfeedback/.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Dalberg" <nospam@.nospam.sss> wrote in message news:20060329013741.245$vZ@.newsreader.com...
>I have been using Management Studio for a few days and it's full of
> annoyannces.
> 1- I just tried to copy 200 rows from a table from Management Studio to
> another similar table with a column that doesn't allow nulls. So I get the
> same error popup message for each row and I can't cancel from this batch.
> 2- Why can't I multiselect tables and Stored procs to delete like I used to
> in 2000's Enterprise Manager.
> 3- When I script a stored procedure in 2000's Enterprise Manager, I woule
> the create, drop and permission statements ALL in one script. This way I
> was able to recreate the SP in one shot even if it exited. Not in
> Management Studio. The drop and create are two seperate functions. If you
> wanted to script the grant permission, you have to do it from the database
> level and go through a bunch of clicks just to do it for one SP. TIME
> CONSUMIUNG!
> 4- So I decided to script the grant permissions from the database level
> using the drop procedure and tell it to include the object-level
> permissions. The generated script included the drop statements but no
> grant statement. I guess it figured that if you're dropping the SP's, why
> include include the grant statements' My idea was that the drop statements
> are in one line so then I can delete them pretty easy. It didn't work.
> 5- As mentioned in my previous message, the import export wizard stops too
> often and gives cryptic messages without indicating why it stopped in clear
> English. That's why I resorted to copy and paste ( see 1)
> 6- I don't think the import/export wizard is smart enough to transfer the
> child tables first to avoid the foreign key issues. This was a problem in
> 2000
> 7- When the transferring many tables which have identity columns, I had to
> check the "Enable identity insert' checkbox and select 'delete rows in
> destination table'. Is there a way to select this option for all tables
> instead of doing it one table at a time?
> 8- Choosing 'modify' for a table and opening a table give the same exact
> icon and text in the list of opened windows. There's no visual clue when
> trying to select either.
> 9- No 'back' button in the last step of the import export wizard if the
> wizard ran successfuly. This means if I want to select other tables, I have
> to restart the wizard. See 10 below for why it's annoying.
> 10- The wizard doesn't remember my settings. I constantly transfer data
> between two sql servers, the first uses windows authentication (local
> server) and the other uses sql server authentication and the wizard always
> defaults to windows authenitcation for both. I wish there's an option to
> remember type of authentication, my username and password or at least
> remember the type of authentication and username.
> I hope these issues will be addressed in the next version.
> John Dalberg

List of fixes in VS SP1

Can someone please supply a link to RS specific features/fixes in Visual Studio SP1? e.g. what fixes have been made to the printing activex control, or the report viewer control?

Thanks

There is a a link to a page will all the bug fixes here. You need to sign in to passport before you click on the link|||I was hoping to see only those related to RS rather than wade through 398 titles. Thanks anyway.

List of data mining techniques not populated - BI Studio hangs

Hello

I am having the same problem and it is very frustating because I have just reinstalled the OS. I am following the Data Mining tutorial and when it comes to the part of creating a mining structure, the combo box of available algorithms hangs and therefore I cannot continue. I would have expected that a fresh installation would not have such a problem.

My environment is:
- OS: Windows XP Professional SP2 with all the updates (fresh installation)
- SQL Server 2005 and Analysis Server 2005 SP2 Developer Edition (fresh installation, running on my machine -not a network service)
- Running Services: SQL Server, AS, SQL Browser (default instances, not-named)
- Visual Studio 2005 Pro SP1
- Available protocols: Share Memory, Named Pipes, TCP/IP
- Machine: Dell Laptop, Centrino Duo 1.66Ghz with 1Gb RAM.

What can I do to prevent this from happening, besides reinstalling the OS or the SQL Server?

Thanks a lot for your help.

Ernesto que tal!!

Rojo

|||

Escribeme cuando puedas a juanbarco@.gmail.com

Rojo

List of data mining techniques not populated - BI Studio hangs

Hello

I am having the same problem and it is very frustating because I have just reinstalled the OS. I am following the Data Mining tutorial and when it comes to the part of creating a mining structure, the combo box of available algorithms hangs and therefore I cannot continue. I would have expected that a fresh installation would not have such a problem.

My environment is:
- OS: Windows XP Professional SP2 with all the updates (fresh installation)
- SQL Server 2005 and Analysis Server 2005 SP2 Developer Edition (fresh installation, running on my machine -not a network service)
- Running Services: SQL Server, AS, SQL Browser (default instances, not-named)
- Visual Studio 2005 Pro SP1
- Available protocols: Share Memory, Named Pipes, TCP/IP
- Machine: Dell Laptop, Centrino Duo 1.66Ghz with 1Gb RAM.

What can I do to prevent this from happening, besides reinstalling the OS or the SQL Server?

Thanks a lot for your help.

Ernesto que tal!!

Rojo

|||

Escribeme cuando puedas a juanbarco@.gmail.com

Rojo

List of data mining techniques not populated - BI Studio hangs

From within the Business Intelligence Studio, I've created a data source and a data source view. Next, when I try to build a mining structure from a relational database, the studio hangs when it goes to the screen which lists the data mining techniques.

Has anybody come across this issue? What needs to be done to get further?

I'm using the June CTP of SQL Server 2005.

Thanks,
-GB.

In http://www.sqlserverdatamining.com I found a post talking about the same problem; the suggested fix was that a previous installation of Analysis Services be removed.

In my case, there was no previous installation of Analysis Services. However, there was an installation of SQL Server 2005 Express Edition, which the install program for SQL Server 2005 CTP detected, and asked me to remove (including a Beta of .NET version 2.0).

-GB.|||

A few questions:

Can you connect to your AS instance from SQL Mgmt Studio or another client?

Are you running a named instance? If yes, make sure that the SQL Server Browser service is functioning correctly.

Do you have network connectivity? There is a known issue with client-server connectivity when you're off the network.

|||Thank you for your reply, Raman.

I can connect to the AS instance from within SQL Management Studio. I can see the data source and data source views that I've already saved before starting with the data mining structure creation. I do not use any other client to connect to this instance.

I am using a default non-named instance.

About network connectivity: I have the TCP / IP stack running, but it is not (always) connected to a network. I also have the ZoneAlaram personal software firewall running. I've turned this off, but it still does not work.

Thanks,
-GB.|||This might be the result of a configuration/install bug. I would suggest uninstalling your June CTP version and moving to the RTM build.

List of data mining techniques not populated - BI Studio hangs

From within the Business Intelligence Studio, I've created a data source and a data source view. Next, when I try to build a mining structure from a relational database, the studio hangs when it goes to the screen which lists the data mining techniques.

Has anybody come across this issue? What needs to be done to get further?

I'm using the June CTP of SQL Server 2005.

Thanks,
-GB.

In http://www.sqlserverdatamining.com I found a post talking about the same problem; the suggested fix was that a previous installation of Analysis Services be removed.

In my case, there was no previous installation of Analysis Services. However, there was an installation of SQL Server 2005 Express Edition, which the install program for SQL Server 2005 CTP detected, and asked me to remove (including a Beta of .NET version 2.0).

-GB.|||

A few questions:

Can you connect to your AS instance from SQL Mgmt Studio or another client?

Are you running a named instance? If yes, make sure that the SQL Server Browser service is functioning correctly.

Do you have network connectivity? There is a known issue with client-server connectivity when you're off the network.

|||Thank you for your reply, Raman.

I can connect to the AS instance from within SQL Management Studio. I can see the data source and data source views that I've already saved before starting with the data mining structure creation. I do not use any other client to connect to this instance.

I am using a default non-named instance.

About network connectivity: I have the TCP / IP stack running, but it is not (always) connected to a network. I also have the ZoneAlaram personal software firewall running. I've turned this off, but it still does not work.

Thanks,
-GB.|||This might be the result of a configuration/install bug. I would suggest uninstalling your June CTP version and moving to the RTM build.

List all Stored Procedures used in a VS.NET project

Hi

This is kind of a visual studio question, but pertinent to db' s - due to many changes of our application I know that there are many stored procs in the database that are no longer used. Since I cannot get a list from the programmers, is there any way to get a list out of visual studio, of every SQL stored procedure called in that project? (Going through each form manually will just be too time consuming for me or the programmers)

Anyone know any tricks??
thx
Desthis query can list all the stored procedure names that ur db has...

select name from YOURDBName..sysobjects where xtype='P'|||Sorry, you misundertand - I need to list the procs used in a Visual Studio project, not in a DB..(as I know a lot are not used anymore and want to clean out my db)