Mapping User Defined Data Type to Base Data Type

Hi,

I am trying to map a user-defined datatype to it's base data type in SQL Server 2000/2005. Let's say I have created a udt named ssn which is actually a char datatype with length 9. I need a query that would map the udt with the base datatype and give the typename of both. I have been using the sys.types table but I still can't see the link. Any help would be appreciated.

Thanks

Here is an Information_Schema view that I created a long time ago in my databases. Most of it I actually copied from a sql 2000 system sproc. You should be able to use it to construct what you need:

SELECT TOP 100 PERCENT
*,
ColumnName + ' ' +
UsedDataType +
CASE WHEN UserDefinedDataType IS NULL THEN
CASE WHEN collationID IS NOT NULL THEN
'('+CAST(CharacterMaxLength AS VARCHAR(10))+')'
ELSE ''
END
ELSE
''
END
AS ColumnDefinition,
ColumnName + ' ' +
UPPER(basedatatype) +
CASE WHEN collationID IS NOT NULL THEN '('+CAST(CharacterMaxLength AS VARCHAR(10))+')' ELSE '' END
AS ColumnDefinition2,
UsedDataType +
CASE WHEN UserDefinedDataType IS NULL THEN
CASE WHEN collationID IS NOT NULL THEN
'('+CAST(CharacterMaxLength AS VARCHAR(10))+')'
ELSE ''
END
ELSE
''
END
AS DefinedDataType
FROM
(
SELECT
DB_NAME() AS DatabaseName,
CASE obj.xtype WHEN 'U' THEN 'TABLE' WHEN 'V' THEN 'VIEW' WHEN 'P' THEN 'PROCEDURE' END AS ObjectType,
USER_NAME(obj.uid) AS TableSchema,
obj.name AS TableName,
col.name AS ColumnName,
col.colid AS ColumnPosition,
com.text AS DefaultValue,
CASE col.isnullable WHEN 1 THEN 'YES' ELSE 'NO' end AS IsNullable,
spt_dtp.LOCAL_TYPE_NAME AS BaseDataType,
CASE WHEN typ.xusertype > 256 THEN typ.name ELSE UPPER(typ.name) END AS UsedDataType,
CONVERT(INT, OdbcPrec(col.xtype, col.length, col.xprec) + spt_dtp.charbin) AS CharacterMaxLength,
NULLIF(col.xprec, 0) AS NumericPrecision,
col.scale AS NumericScale,
CONVERT(SYSNAME, CASE WHEN typ.xusertype > 256 THEN typ.name ELSE NULL END) AS UserDefinedDataType,
OBJECT_NAME(cdefault) AS ColumnDefaultName ,
typ.CollationID
FROM
sysobjects obj,
master.dbo.spt_datatype_info spt_dtp,
systypes typ,
syscolumns col
LEFT OUTER JOIN syscomments com on col.cdefault = com.id AND com.colid = 1,
master.dbo.syscharsets a_cha
WHERE
obj.id = col.id AND
typ.xtype = spt_dtp.ss_dtype AND
(spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2) AND
obj.xtype in ('U', 'V', 'P') AND
col.xusertype = typ.xusertype AND
(
spt_dtp.AUTO_INCREMENT IS NULL OR spt_dtp.AUTO_INCREMENT = 0) AND
a_cha.id = ISNULL(CONVERT(TINYINT, CollationPropertyFromID(col.collationid, 'sqlcharset')),
CONVERT(TINYINT, ServerProperty('sqlcharset'))
) and obj.type = 'u'

) a
ORDER BY
TableName, ColumnPosition ASC

Mapping Columns Automatically?

New to SSIS...

I created a new package with a source and destination and manually created the output column with data type, etc. Works. The issue is say the table has 200 columns to export.. I dont want to create these by hand. How can I just say export them all to csv format and not have to specify and map each and every column?

Use the Export Data Wizard in SSMS.

-Jamie

|||Thats fine and dandy when starting from scratch. But if you have spent a lot of time building scripts and other actions in an existing package... it seems that it should be simple to add all columns to an existing text export. This seems like it would be such a common issue there has to be a solution.|||

You could replace your existing source adapter with a new one. The default behaviour is to select all columns which by the sound of it is what you want.

The new columns will automatically appear in the metadata of downstream components.

-Jamie

|||Thanks.. I will try that and see how it goes.

Mapping a user to a login

I have created a login and a user on a database using SMO. I am having trouble mapping a database to the login. When you create a login from the GUI using SQL Server Management Studio Express CTP the user is automatically created and the database is mapped to this login from the user mapping tab. When I create the login programmatically this is not the case so I need to create the user. How do I map the database to the login. I set the default database property on the login but this does not do it.

Thanks,

Michael Gustafson

This is the pattern you would normally follow (this is for a windows user, but it works the same for a SQL user):

Login l = new Login(svr, @."REDMOND\mwories");
l.LoginType = LoginType.WindowsUser;
l.Create();

Database db = svr.Databases["AdventureWorks"];

User u = new User(db, @."REDMOND\mwories");
u.Login = l.Name;
u.UserType = UserType.SqlLogin;
u.Create();

This maps the database user to the login as you create the login.

|||

what property of the Login is used to show what databases are mapped?

also is there a way with SMO to check and see if a password is blank? Granted the odds of that are extemely slimmer with 2005.

|||

I found the DatabaseMapping() class and was able to find which logins were missing mappings. Im still at a lost at how to test for blank passwords using SQL2005 and SMO.

Thanks for taking time to read this.

Chuck Turner

Mapping a user to a login

I have created a login and a user on a database using SMO. I am having trouble mapping a database to the login. When you create a login from the GUI using SQL Server Management Studio Express CTP the user is automatically created and the database is mapped to this login from the user mapping tab. When I create the login programmatically this is not the case so I need to create the user. How do I map the database to the login. I set the default database property on the login but this does not do it.

Thanks,

Michael Gustafson

This is the pattern you would normally follow (this is for a windows user, but it works the same for a SQL user):

Login l = new Login(svr, @."REDMOND\mwories");
l.LoginType = LoginType.WindowsUser;
l.Create();

Database db = svr.Databases["AdventureWorks"];

User u = new User(db, @."REDMOND\mwories");
u.Login = l.Name;
u.UserType = UserType.SqlLogin;
u.Create();

This maps the database user to the login as you create the login.

|||

what property of the Login is used to show what databases are mapped?

also is there a way with SMO to check and see if a password is blank? Granted the odds of that are extemely slimmer with 2005.

|||

I found the DatabaseMapping() class and was able to find which logins were missing mappings. Im still at a lost at how to test for blank passwords using SQL2005 and SMO.

Thanks for taking time to read this.

Chuck Turner

MAPI profil

Hi,
I try e-mail through SQL Server. I have Outlook 2000 installed, MAPI profile created. If I want test it, SQL Server displays this error:
Error 18030: xp_test_mapi_profile : Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.
Can me sombody help, thanks.Hi
You need to login as the SQL Agent on the machine, configure outlook, log
off and re-start SQL Agent.
See the following KB article on exactly how to do it.
http://support.microsoft.com/default.aspx?scid=kb;en-us;263556&Product=sql2k
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Ondrik" <anonymous@.discussions.microsoft.com> wrote in message
news:80987291-A063-4B4A-BBDB-6DC363DD2849@.microsoft.com...
> Hi,
> I try e-mail through SQL Server. I have Outlook 2000 installed, MAPI
profile created. If I want test it, SQL Server displays this error:
> Error 18030: xp_test_mapi_profile : Either there is no default mail client
or the current mail client cannot fulfill the messaging request. Please run
Microsoft Outlook and set it as the default mail client.
> Can me sombody help, thanks.|||Hmmm, it is terrible. I have it so done. Same domain account for log on machine, SQL Server and SQL Agent. What mean in error message: "Please run Microsoft Outlook and set it as the default mail client." How? I don't want reinstall server.

Many-to-Many Variation

In establishing a many-to-many relationship between two tables, a 3rd
"junction" table is created that contains at least two foreign keys.
No problem.
But - what is a recommended way to proceed to create a many-to-many
relationship between (1) one table, and (2) two other tables.
For example, say you are designing a database for a school and you are
establishing a many-to-many relationship between [Classes] and [Students]
and [Teachers].
There would obviously be a Classes table. A "junction table" could then have
foreign keys to Classes and Students. That would get us a many-to-many
relationship between Classes and Students. But we also need to show that
relationship between Classes and Teachers.
What do we do with the "junction table"? Do we add a 3rd foreign key to the
existing table? Do we create another junction table altogether?
I'd appreciate some guidance on this.
Thanks!"Smithers" <A@.B.COM> wrote in message
news:OSoQrlVlGHA.3740@.TK2MSFTNGP02.phx.gbl...
> In establishing a many-to-many relationship between two tables, a 3rd
> "junction" table is created that contains at least two foreign keys.
> No problem.
> But - what is a recommended way to proceed to create a many-to-many
> relationship between (1) one table, and (2) two other tables.
> For example, say you are designing a database for a school and you are
> establishing a many-to-many relationship between [Classes] and [Students]
> and [Teachers].
> There would obviously be a Classes table. A "junction table" could then
> have foreign keys to Classes and Students. That would get us a
> many-to-many relationship between Classes and Students. But we also need
> to show that relationship between Classes and Teachers.
> What do we do with the "junction table"? Do we add a 3rd foreign key to
> the existing table? Do we create another junction table altogether?
>
Assuming that a class has many teachers, you would use seperate junction
table. Each junction table models a different relation. One models the
"student is a member of class" relation, and the other models "class is
taught by teacher" relation.
If each class has a single teacher, then add a foreign key on the Classes
table referencing Teachers.
David|||Google up "Fifth Normal Form (5NF)"|||And then if you want to model the relationship between Teachers and Students
...
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OUsBopVlGHA.2128@.TK2MSFTNGP04.phx.gbl...
> "Smithers" <A@.B.COM> wrote in message
> news:OSoQrlVlGHA.3740@.TK2MSFTNGP02.phx.gbl...
> Assuming that a class has many teachers, you would use seperate junction
> table. Each junction table models a different relation. One models the
> "student is a member of class" relation, and the other models "class is
> taught by teacher" relation.
> If each class has a single teacher, then add a foreign key on the Classes
> table referencing Teachers.
> David|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:Ol7roDWlGHA.408@.TK2MSFTNGP03.phx.gbl...
> And then if you want to model the relationship between Teachers and
> Students ...
>
That information can be derived from the existing relations:
Select Class.Teacher, StudentClass.Student
from Class
join StudentClass
on Class.ID = StudentClass.ID
David|||I disagree.
But my disagreement is positioned on having had to work out this kind of
issue before. (I may be overlooking some simple solution you have worked
out. )
Student A has a Class B
StudentClass:
StudentClass_ID = 1, Student=A, Class=B
Teacher ZZ teaches Class B, Teacher YY also teaches Class B.
Class:
Class_ID=1,Class=B, Teacher=YY
Class_ID=2,Class=B, Teacher=ZZ
Student A's Class B is taught by Teacher ZZ
Your query will tell me that Student A has a relationship with Teacher YY
which is NOT true.
More has to be done to develop a relationship between Teachers and Students.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:elGU93WlGHA.2392@.TK2MSFTNGP04.phx.gbl...
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:Ol7roDWlGHA.408@.TK2MSFTNGP03.phx.gbl...
>
> That information can be derived from the existing relations:
> Select Class.Teacher, StudentClass.Student
> from Class
> join StudentClass
> on Class.ID = StudentClass.ID
> David
>
>|||Arnie Rowland wrote:
> I disagree.
> More has to be done to develop a relationship between Teachers and Student
s.
> --
Correct. This problem is solved by 5th Normal Form.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I don't know if I am good enough to interefere in the clash of the titans.
But Can't we have something like this?
Take it simply.. A student is associated to a particular class by a
particular teacher.
And there is no explicit teacher student relation.
The same example you had quoted.
Student A has a Class B
StudentClass:
StudentClass_ID = 1, Student=A, Class=B
Teacher ZZ teaches Class B, Teacher YY also teaches Class B.
Class:
Class_ID=1,Class=B, Teacher=YY
Class_ID=2,Class=B, Teacher=ZZ
If Student A's Class B is taught by Teacher ZZ
The the student class table should look like this
StudentClass:
StudentClass_ID = 1, Student=A, Class_ID=2
If a student is attending a session, it needs a class and a teacher.
and if such a class is happening then we should be having an entry in the
class table. Am I missing something?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi David (and all in the discussion),
To tell the truth, I haven't really bothered about 5NF till now.Assumed
anything above 3rd was not practical :)
But the post yesterday and the replies intrigues me. I had checked up on a
few websites and the examples that is given there doesn't convince me. Can
you direct me to a website where the explaination is given. And If I am not
able to understand, then I can discuss with reference to that.
Thanks a lot.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"David Portas" wrote:

> Arnie Rowland wrote:
> Correct. This problem is solved by 5th Normal Form.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Manually Creating Membership Tables

Hi, I have limited control on the server I use. I can not create data bases but can add tables and edit the data base that has been created for me. So what I would like to do is be able to use a tool such as aspnet_regsql through SQL Server Express to add the tables that Membership would automatically create. Any help in pointing me in the right direction would be great

Thanks

howlinhuskie

Hi,

if you're able to execute sql statements on the server you could use the aspnet_regsql tool to create all the necessary sql statements:http://forums.asp.net/p/994473/1298256.aspx#1298256.

Grz, Kris.

|||

Thanks for looking in on me Kris. Can the aspnet_regsql tool be run using SQL Server Express or do you have to run it through the dos command line? Is there step by step instructions anywhere?

Any help would be great

howlinhuskie

|||

Hi,

indeed you have to run it from the command line.

Grz, Kris.

|||

Hi,

also take a look at this article:Create Membership tables in another database than the standard aspnetdb.mdf.

Grz, Kris.

|||

Thanks again Kris, I do not have the permissions on the server to access the command line.

Mike

|||

howlinhuskie:

I do not have the permissions on the server to access the command line.

And from your own developer pc? If you already have a database and know its name you can use the command line tool from your dev pc, generate the sql statements and later on use Query Analyzer or SQL Server Management Studio to run those queries on the remote database server. You do have permissions to run sql statements on your SQL Server right? In case not you could still generate it and have a DBA install it.

Grz, Kris.

Manual Log Shipping Restore Log Problems

Hi,
I am implementing manual log shipping between a production server and a
standby server.
I have done the follwing:
1. On prod server created 2 devices for db and log.
2. created 2 jobs which will backup db/log,move to network share and restore
to standby server using something like this
BACKUP LOG newnorthwind TO backup_log WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'
3. my problem is that when scheduling the log every 15 mins the log will
still have the same file name and even i tried having datetime stamp but the
backup time may differ from one log to other and the restore has to have some
kind of dynamic settings that will find the last log backup time.
Can anyone suggest as how i am do that and i dont want to erase the log file.
Mnay thanks
Anup
Keep a table that keeps track of which log files you've already "Shipped"
Greg Jackson
PDX, Oregon
|||But How do i get that info when I am restoring the logs to the standby table
"pdxJaxon" wrote:

> Keep a table that keeps track of which log files you've already "Shipped"
>
> Greg Jackson
> PDX, Oregon
>
>
|||you name the t-logs with a datetime in the name
DBNAME_TLOG_200506080800.trn
have a process that copies all the trn logs over to the target server
on the target server read al files in the directory and put them in a temp
table sorted by name
this table is (LogsToApply)
walk the records one at a time and apply them in order IF THEY are not
already listed in the LogsApplied Table.
once you've applied a log, add it's name to the LogsApplied table.
that's it.
I could probably dig the script up for you, but it's been over 2 years since
I did this.
It works great.
GAJ

Manual Log Shipping Restore Log Problems

Hi,
I am implementing manual log shipping between a production server and a
standby server.
I have done the follwing:
1. On prod server created 2 devices for db and log.
2. created 2 jobs which will backup db/log,move to network share and restore
to standby server using something like this
BACKUP LOG newnorthwind TO backup_log WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'
3. my problem is that when scheduling the log every 15 mins the log will
still have the same file name and even i tried having datetime stamp but the
backup time may differ from one log to other and the restore has to have som
e
kind of dynamic settings that will find the last log backup time.
Can anyone suggest as how i am do that and i dont want to erase the log file
.
Mnay thanks
AnupKeep a table that keeps track of which log files you've already "Shipped"
Greg Jackson
PDX, Oregon|||But How do i get that info when I am restoring the logs to the standby table
"pdxJaxon" wrote:

> Keep a table that keeps track of which log files you've already "Shipped"
>
> Greg Jackson
> PDX, Oregon
>
>|||you name the t-logs with a datetime in the name
DBNAME_TLOG_200506080800.trn
have a process that copies all the trn logs over to the target server
on the target server read al files in the directory and put them in a temp
table sorted by name
this table is (LogsToApply)
walk the records one at a time and apply them in order IF THEY are not
already listed in the LogsApplied Table.
once you've applied a log, add it's name to the LogsApplied table.
that's it.
I could probably dig the script up for you, but it's been over 2 years since
I did this.
It works great.
GAJ

Manual Log Shipping Restore Log Problems

Hi,
I am implementing manual log shipping between a production server and a
standby server.
I have done the follwing:
1. On prod server created 2 devices for db and log.
2. created 2 jobs which will backup db/log,move to network share and restore
to standby server using something like this
BACKUP LOG newnorthwind TO backup_log WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'
3. my problem is that when scheduling the log every 15 mins the log will
still have the same file name and even i tried having datetime stamp but the
backup time may differ from one log to other and the restore has to have some
kind of dynamic settings that will find the last log backup time.
Can anyone suggest as how i am do that and i dont want to erase the log file.
Mnay thanks
AnupKeep a table that keeps track of which log files you've already "Shipped"
Greg Jackson
PDX, Oregon|||But How do i get that info when I am restoring the logs to the standby table
"pdxJaxon" wrote:
> Keep a table that keeps track of which log files you've already "Shipped"
>
> Greg Jackson
> PDX, Oregon
>
>|||you name the t-logs with a datetime in the name
DBNAME_TLOG_200506080800.trn
have a process that copies all the trn logs over to the target server
on the target server read al files in the directory and put them in a temp
table sorted by name
this table is (LogsToApply)
walk the records one at a time and apply them in order IF THEY are not
already listed in the LogsApplied Table.
once you've applied a log, add it's name to the LogsApplied table.
that's it.
I could probably dig the script up for you, but it's been over 2 years since
I did this.
It works great.
GAJ

Manipulating the result set of one stored procedure from another....

Hi,

I have one stored procedure that calls another ( EXEC proc_abcd ). I would
like to return a result set (a temporary table I have created in the
procedure proc_abcd) to the calling procedure for further manipulation. How
can I do this given that TABLE variables cannot be passed into, or returned
from, a stored procedure?

Thanks,

Robin

Example: (if such a thing were possible):

DECLARE @.myTempTable1 TABLE ( ID INT NOT NULL )
DECLARE @.myTempTable2 TABLE ( ID INT NOT NULL )

....
/*
Insert a test value into the first temporary table
*/

INSERT INTO @.myTempTable1 VALUES ( 1234 )
....

/*
Execute a stored procedure returning another temporary table of
values.
*/

EXEC proc_abcd @.myTempTable2 OUTPUT

...
...

/*
Insert the values from the second temporary table into the first.
*/

SELECT * INTO @.myTempTable1 FROM @.myTempTable2Robin Tucker (idontwanttobespammedanymore@.reallyidont.com) writes:
> I have one stored procedure that calls another ( EXEC proc_abcd ). I
> would like to return a result set (a temporary table I have created in
> the procedure proc_abcd) to the calling procedure for further
> manipulation. How can I do this given that TABLE variables cannot be
> passed into, or returned from, a stored procedure?

Have a look at http://www.sommarskog.se/share_data.html where I discuss
various techniques.

> SELECT * INTO @.myTempTable1 FROM @.myTempTable2

You cannot do a SELECT INTO with a table variable.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Manintenance Plan

I've created a maintenance plan and getting the following error message in the event log. This has been working for a while problem happend a week a ago, no changes to the system. Novice SQL user, any help on this appreciated.

Event ID 208. SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'RSS Pro2000 DBMP'' (0xFE3D7C9C154F9E48A4AA953C88D9F97E) - Status: Failed - Invoked on: 2007-03-19 13:28:00 - Message: The job failed. The Job was invoked by Schedule 13 (Schedule 1). The last step to run was step 1 (Step 1).

Are there any changes to the recovery model during this time of execution?

Also check the password or any information pertaining to SQLAgent account used here.

http://www.sqlservercentral.com/columnists/aingold/workingaround2005maintenanceplans.asp fyi.

|||No changes made. SQLAgent using 'Local System' a/c|||

Try to execute the job as manually with your account credential.

Do you have any other databases scheduled in this database?

if so are they getting same error?

|||The Db's are being backed up (there's 4 in total). It's only the transaction log that are not running.I've changed the a/c to 'administrator' and ran another transaction log and getting the same message.|||Have you applied the service pack or any changes to the server recentlY?

Managing reporting services models

Hi everybody,

I have the following scenario: I have web application which is creting new SQL Server database each time when new customer is created in the application.

I would like to give users the possibility of creating ad hoc reports and thus I need to create new connection and report model (and deploy them to my reoport server) each time when the new database is created (report models can not use multiple databases). Does anybody knows how can I do that?

Maybe there is another approach to this kind of problem?

Thank you in advance,

Marek

You can create datasources and autogenerate models from these datasources using the SSRS SOAP API. Check out this article in BOL for information on how to get started using the SOAP API:

http://msdn2.microsoft.com/en-us/library/ms159162.aspx

Managing Jobs - server roles

Hello,
What server/permission role can i assign a user so he can use SQL
agent/management and see that jobs have run, created etc without assigning
him system admin. The most restrictive yet allowing him to see the jobs
thanksHi,
A user who is not a member of the sysadmin fixed role can use sp_help_job to
view only the jobs he/she owns.
Thanks
Hari
"Charlie" <Charlie@.discussions.microsoft.com> wrote in message
news:4DAEB3D9-4FA6-41C0-BCF3-E0795BFC1031@.microsoft.com...
> Hello,
> What server/permission role can i assign a user so he can use SQL
> agent/management and see that jobs have run, created etc without assigning
> him system admin. The most restrictive yet allowing him to see the jobs
> thanks

Management Studio: Project Hierarchy

I created a project type "SQL Server Scripts" in "Microsoft SQL Server
Management Studio", and the default structure created was:
+ Project Name
+ Connections (Type 2)
+ Queries (Type 0)
+ Miscellaneous (Type 3)
How can I create new folders? I'd like to organize my queries by subject
(Finance, Human Resources, Marketing, etc)
I tried to edit the project file (*.ssmssqlproj) to add new Logical Folders,
but it seems those folders are based on "Type" and you cannot have multiple
folders of the same type.
<LogicalFolder Name="Queries" Type="0">
After frustrating attempts, I decided to use the existing hierarchy. I added
my existing stored procedures to the "Queries" folder and realized the files
were not in alphabetical order. It's impossible to find anything when list
contains more than a thousand queries.
Am I using the SQL MANAGEMENT STUDIO correctly? If so, what's the purpose of
creating a SQL SCRIPT PROJECT if you cannot organize the files the way you
want? Please help!!!
I posted the same question a few days ago, and I also played around with the
project file to no avial. Someone replied then that this is an outstanding
issue with Microsoft. Please go to the site, vote on the issue, and add your
voice. If we don't complain, MS isn't going to do anything about it:
http://lab.msdn.microsoft.com/produc...7-be25db5080df
My feeling is that the whole VS 2005 suite, including Management Studio and
Team System, was rushed out and shipped half cooked.
Having replaced the graceful Query Analyzer and Enterprise Manager with a
lumberous piece of bloatware (VS 2005 engine), you'd expect them to
compensate the developers/DBA's by allowing them to integrate SSMS with VS
2005 solutions or at least give them the ability to organize their projects
in a meaningful way.
"YYZRic" wrote:

> I created a project type "SQL Server Scripts" in "Microsoft SQL Server
> Management Studio", and the default structure created was:
> + Project Name
> + Connections (Type 2)
> + Queries (Type 0)
> + Miscellaneous (Type 3)
> How can I create new folders? I'd like to organize my queries by subject
> (Finance, Human Resources, Marketing, etc)
> I tried to edit the project file (*.ssmssqlproj) to add new Logical Folders,
> but it seems those folders are based on "Type" and you cannot have multiple
> folders of the same type.
> <LogicalFolder Name="Queries" Type="0">
> After frustrating attempts, I decided to use the existing hierarchy. I added
> my existing stored procedures to the "Queries" folder and realized the files
> were not in alphabetical order. It's impossible to find anything when list
> contains more than a thousand queries.
> Am I using the SQL MANAGEMENT STUDIO correctly? If so, what's the purpose of
> creating a SQL SCRIPT PROJECT if you cannot organize the files the way you
> want? Please help!!!
>
>
>
|||Thanks Buddie, I cast my vote!
"Homam" wrote:
[vbcol=seagreen]
> I posted the same question a few days ago, and I also played around with the
> project file to no avial. Someone replied then that this is an outstanding
> issue with Microsoft. Please go to the site, vote on the issue, and add your
> voice. If we don't complain, MS isn't going to do anything about it:
> http://lab.msdn.microsoft.com/produc...7-be25db5080df
> My feeling is that the whole VS 2005 suite, including Management Studio and
> Team System, was rushed out and shipped half cooked.
> Having replaced the graceful Query Analyzer and Enterprise Manager with a
> lumberous piece of bloatware (VS 2005 engine), you'd expect them to
> compensate the developers/DBA's by allowing them to integrate SSMS with VS
> 2005 solutions or at least give them the ability to organize their projects
> in a meaningful way.
>
>
> "YYZRic" wrote: