How to Script Muplitple SPROCS/VIEWS at one time

In Enterprise Manager one can select several SPROCS/VIEWS using the
CONTROL key and then Right-Click to script out those objects.
Alternativly, pressing CONTROL-C copies, to the clipboard, the T-SQL to
create the selected objects.
SQL Management Studio seems to only allow you to script one object at a
time.
Is there a way in SQL Management Studio to select multiple objects and
generate create or modify scripts?Hi, naviagte to the database node and script the objects using the
wizard, select only the procedures you want to script.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

How to scale down database diagram to fit into a page for SQL 2005?

In sql 2000 enterprise manager, diagram, I can right click and get page setup where I can shrink the size of the diagram to fit page. In the SQL 2005 diagram, How can I adjust the size of the image printed on paper?

Did Microsoft take this useful feature away?

Steve,

Did you ever figure out how to do this? I am having the same problem now. There has got to be a way to re-size this drawing.

Thanks,

Andy

|||

I figured it out and wrote this doc for my development team.

How To Use Diagram In SQL 2005

Stephan Loh

The Database Designer is a visual tool that allows you to design and visualize a database to which you are connected. When designing a database, you can use Database Designer to create, edit, or delete tables, columns, keys, indexes, relationships, and constraints. To visualize a database, you can create one or more diagrams illustrating some or all of the tables, columns, keys, and relationships in it.(Microsoft Book online)

SQL 2005 allows you to create database diagram (ER diagram, or schema diagram).You can also design database by using this diagram.

1. In Microsoft SQL server Management Studio, Object Explorer, double click on the database you want to work with, right click on Database Diagrams.

2. Select New Database Diagram, add tables.

3. To get print view, right click on the page (Not on a table), select View Page Breaks. This allows you to see the diagram by page.

4. To adjust the size of table,in Microsoft SQL server Management Studio, Click on File at top left corner, select Page Setup.In Page Setup, adjust Print Scale to shrink the size of tables.You can also adjust Orientation, and Select paper size.

5. To make the table show all columns, select one or more tables, right click on one of the tables, and select Autosize Selected Tables.

6. To zoom, (4 ways to zoom), I find the best way is to hold down Ctrl Key, turn the wheel on your mouse.

7. To see more than column name in diagram, highlight tables you want to display other information, right click on one of the selected tables, select Table View,You can select Standard or Other options,if you want to see other information, you will need to select Modify Custom, and add information you want to see.After you click OK, you will need to right click on one of the selected tables, select Table View,Custom.

8. To modify column definition, you can directly click on the cell of column definition to modify it.For example, to modify column name, you click on the column name, and type in new name.

9. To modify a table Name (table property), highlight the table, press F4.

10. To modify a single column,highlight the column, press F4.

11. To add a table, right click on the page (not on a table), and select New Table to start design a new table

12. Add a relationship (Foreign Key Constraint), right click on the foreign key table, and select Relationships.Click on the button on the right side of Tables and Columns Specificationto designate primary key table.

13. To print,in order to make sure WYSIWYG, right click on the page (not on a table), select Recalculate Page Breaks.Review your table layout on computer before you print.

|||Thanks! That is excellent.

-Andy

How to scale down database diagram to fit into a page for SQL 2005?

In sql 2000 enterprise manager, diagram, I can right click and get page setup where I can shrink the size of the diagram to fit page. In the SQL 2005 diagram, How can I adjust the size of the image printed on paper?

Did Microsoft take this useful feature away?

Steve,

Did you ever figure out how to do this? I am having the same problem now. There has got to be a way to re-size this drawing.

Thanks,

Andy

|||

I figured it out and wrote this doc for my development team.

How To Use Diagram In SQL 2005

Stephan Loh

The Database Designer is a visual tool that allows you to design and visualize a database to which you are connected. When designing a database, you can use Database Designer to create, edit, or delete tables, columns, keys, indexes, relationships, and constraints. To visualize a database, you can create one or more diagrams illustrating some or all of the tables, columns, keys, and relationships in it.(Microsoft Book online)

SQL 2005 allows you to create database diagram (ER diagram, or schema diagram).You can also design database by using this diagram.

1. In Microsoft SQL server Management Studio, Object Explorer, double click on the database you want to work with, right click on Database Diagrams.

2. Select New Database Diagram, add tables.

3. To get print view, right click on the page (Not on a table), select View Page Breaks. This allows you to see the diagram by page.

4. To adjust the size of table,in Microsoft SQL server Management Studio, Click on File at top left corner, select Page Setup.In Page Setup, adjust Print Scale to shrink the size of tables.You can also adjust Orientation, and Select paper size.

5. To make the table show all columns, select one or more tables, right click on one of the tables, and select Autosize Selected Tables.

6. To zoom, (4 ways to zoom), I find the best way is to hold down Ctrl Key, turn the wheel on your mouse.

7. To see more than column name in diagram, highlight tables you want to display other information, right click on one of the selected tables, select Table View,You can select Standard or Other options,if you want to see other information, you will need to select Modify Custom, and add information you want to see.After you click OK, you will need to right click on one of the selected tables, select Table View,Custom.

8. To modify column definition, you can directly click on the cell of column definition to modify it.For example, to modify column name, you click on the column name, and type in new name.

9. To modify a table Name (table property),highlight the table, press F4.

10. To modify a single column,highlight the column, press F4.

11. To add a table, right click on the page (not on a table), and select New Table to start design a new table

12. Add a relationship (Foreign Key Constraint), right click on the foreign key table,and select Relationships.Click on the button on the right side of Tables and Columns Specificationto designate primary key table.

13. To print,in order to make sure WYSIWYG, right click on the page (not on a table), select Recalculate Page Breaks.Review your table layout on computer before you print.

|||Thanks! That is excellent.

-Andy

How to scale down database diagram to fit into a page for SQL 2005?

In sql 2000 enterprise manager, diagram, I can right click and get page setup where I can shrink the size of the diagram to fit page. In the SQL 2005 diagram, How can I adjust the size of the image printed on paper?

Did Microsoft take this useful feature away?

Steve,

Did you ever figure out how to do this? I am having the same problem now. There has got to be a way to re-size this drawing.

Thanks,

Andy

|||

I figured it out and wrote this doc for my development team.

How To Use Diagram In SQL 2005

Stephan Loh

The Database Designer is a visual tool that allows you to design and visualize a database to which you are connected. When designing a database, you can use Database Designer to create, edit, or delete tables, columns, keys, indexes, relationships, and constraints. To visualize a database, you can create one or more diagrams illustrating some or all of the tables, columns, keys, and relationships in it.(Microsoft Book online)

SQL 2005 allows you to create database diagram (ER diagram, or schema diagram).You can also design database by using this diagram.

1. In Microsoft SQL server Management Studio, Object Explorer, double click on the database you want to work with, right click on Database Diagrams.

2. Select New Database Diagram, add tables.

3. To get print view, right click on the page (Not on a table), select View Page Breaks. This allows you to see the diagram by page.

4. To adjust the size of table,in Microsoft SQL server Management Studio, Click on File at top left corner, select Page Setup.In Page Setup, adjust Print Scale to shrink the size of tables.You can also adjust Orientation, and Select paper size.

5. To make the table show all columns, select one or more tables, right click on one of the tables, and select Autosize Selected Tables.

6. To zoom, (4 ways to zoom), I find the best way is to hold down Ctrl Key, turn the wheel on your mouse.

7. To see more than column name in diagram, highlight tables you want to display other information, right click on one of the selected tables, select Table View,You can select Standard or Other options,if you want to see other information, you will need to select Modify Custom, and add information you want to see.After you click OK, you will need to right click on one of the selected tables, select Table View,Custom.

8. To modify column definition, you can directly click on the cell of column definition to modify it.For example, to modify column name, you click on the column name, and type in new name.

9. To modify a table Name (table property), highlight the table, press F4.

10. To modify a single column,highlight the column, press F4.

11. To add a table, right click on the page (not on a table), and select New Table to start design a new table

12. Add a relationship (Foreign Key Constraint), right click on the foreign key table, and select Relationships.Click on the button on the right side of Tables and Columns Specificationto designate primary key table.

13. To print,in order to make sure WYSIWYG, right click on the page (not on a table), select Recalculate Page Breaks.Review your table layout on computer before you print.

|||Thanks! That is excellent.

-Andy

how to save entity relationship diagram to a file

I have a diagram created in enterprise manager. How do I save it to a file
and open it from another server through enterprise manager?
Thankssee http://support.microsoft.com/defaul...;en-us;Q320125.
James
"she" wrote:

> I have a diagram created in enterprise manager. How do I save it to a fil
e
> and open it from another server through enterprise manager?
> Thanks|||James:
Thank you so much! The tip also helped me to resolve anther issue.
"James Ma" wrote:
> see http://support.microsoft.com/defaul...;en-us;Q320125.
> James
> "she" wrote:
>

How to Save a query result in ANSI in SQL 2005?

Hi

When I was using Enterprise Manager (SQL 2000) and stored my query result (to file), it was stored in Ansi Encoding.

After I upgrade to SQL Server Management Studio (2005), it seams that when I store the query result to file it's Unicode Encoded. This give me a lot of trouble, when I use other program who I must read this file. For small result set, I can open it in Notepad, save it as ANSI, and then use it in my other program. When the result set is so big that Notepad not can help me, I can't use the stored result set.

There must be a way to store my Query result in Ansi encoding, but I don't know how I can do it. Hope some one know, and can help me solve this big problem.

JF

When the Save Results dialog box Open click the drop down next to the Save button and click Save with Encoding..."

Another dialog box Save With Encoding gives you Available Encodings to choose .

How to run SQL Server Management Studio

We have installed SQL Server 2005 on a machine running Windows 2003. We found
out that there is no Enterprise Manager, but Management Studio. We have no
idea how to run this.
We tried to remove SQL Server, but Add/Remove doesn't remove it. Just only
removes it from the list.
We hope we can get an answer, because we are a serious evaluating SQL 2005
over Oracle 10g.
Thanks for your help.
Matt
Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management
Studio
?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:DD381736-D551-4BB6-9488-D2E61C97E33F@.microsoft.com...
> We have installed SQL Server 2005 on a machine running Windows 2003. We
> found
> out that there is no Enterprise Manager, but Management Studio. We have no
> idea how to run this.
> We tried to remove SQL Server, but Add/Remove doesn't remove it. Just only
> removes it from the list.
> We hope we can get an answer, because we are a serious evaluating SQL 2005
> over Oracle 10g.
> Thanks for your help.
> Matt
|||We would assume that it was that simple, but in the Microsoft SQL Server 2005
menu only Configurations Tools is shown.
We have seen another post with the same problem but they were trying to
install it on XP, while we have installed it on Server 2003.
The installation program seems to run so smooth, so we can't imagine that
something crucial like this was overlooked.
Any help is appreciated.
Matt
"Adam Machanic" wrote:

> Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management
> Studio
> ?
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:DD381736-D551-4BB6-9488-D2E61C97E33F@.microsoft.com...
>
>
|||Did you already go back through the installer and make sure that the correct
options were selected? Also try switching into Advanced mode to be certain.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:5D45FE06-D501-4CC9-8DC0-3C786F6ECFEE@.microsoft.com...[vbcol=seagreen]
> We would assume that it was that simple, but in the Microsoft SQL Server
> 2005
> menu only Configurations Tools is shown.
> We have seen another post with the same problem but they were trying to
> install it on XP, while we have installed it on Server 2003.
> The installation program seems to run so smooth, so we can't imagine that
> something crucial like this was overlooked.
> Any help is appreciated.
> Matt
> "Adam Machanic" wrote:
|||Thanks for your help, but I tried already all of that.
The bizarre thing is that it doesn't remove the installation through
Add/Remove Programs, while it takes it from the list. When you try to install
certain components (through Advanced) then it tells you it is already there.
Another problem is that when you install subcomponents it won't let you
install other subcomponents later.
This product definitely has installation issues and I hope they will be
resolved as the pack will now start evaluating (like us) and they're turned
away before they can even try away. A smooth installation is software
marketing 101.
Any help is appreciated. Even help to remove the product.
"Adam Machanic" wrote:

> Did you already go back through the installer and make sure that the correct
> options were selected? Also try switching into Advanced mode to be certain.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:5D45FE06-D501-4CC9-8DC0-3C786F6ECFEE@.microsoft.com...
>
>

How to run SQL Server Management Studio

We have installed SQL Server 2005 on a machine running Windows 2003. We found
out that there is no Enterprise Manager, but Management Studio. We have no
idea how to run this.
We tried to remove SQL Server, but Add/Remove doesn't remove it. Just only
removes it from the list.
We hope we can get an answer, because we are a serious evaluating SQL 2005
over Oracle 10g.
Thanks for your help.
MattStart -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management
Studio
'
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:DD381736-D551-4BB6-9488-D2E61C97E33F@.microsoft.com...
> We have installed SQL Server 2005 on a machine running Windows 2003. We
> found
> out that there is no Enterprise Manager, but Management Studio. We have no
> idea how to run this.
> We tried to remove SQL Server, but Add/Remove doesn't remove it. Just only
> removes it from the list.
> We hope we can get an answer, because we are a serious evaluating SQL 2005
> over Oracle 10g.
> Thanks for your help.
> Matt|||We would assume that it was that simple, but in the Microsoft SQL Server 2005
menu only Configurations Tools is shown.
We have seen another post with the same problem but they were trying to
install it on XP, while we have installed it on Server 2003.
The installation program seems to run so smooth, so we can't imagine that
something crucial like this was overlooked.
Any help is appreciated.
Matt
"Adam Machanic" wrote:
> Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management
> Studio
> '
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:DD381736-D551-4BB6-9488-D2E61C97E33F@.microsoft.com...
> > We have installed SQL Server 2005 on a machine running Windows 2003. We
> > found
> > out that there is no Enterprise Manager, but Management Studio. We have no
> > idea how to run this.
> >
> > We tried to remove SQL Server, but Add/Remove doesn't remove it. Just only
> > removes it from the list.
> >
> > We hope we can get an answer, because we are a serious evaluating SQL 2005
> > over Oracle 10g.
> >
> > Thanks for your help.
> >
> > Matt
>
>|||Did you already go back through the installer and make sure that the correct
options were selected? Also try switching into Advanced mode to be certain.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:5D45FE06-D501-4CC9-8DC0-3C786F6ECFEE@.microsoft.com...
> We would assume that it was that simple, but in the Microsoft SQL Server
> 2005
> menu only Configurations Tools is shown.
> We have seen another post with the same problem but they were trying to
> install it on XP, while we have installed it on Server 2003.
> The installation program seems to run so smooth, so we can't imagine that
> something crucial like this was overlooked.
> Any help is appreciated.
> Matt
> "Adam Machanic" wrote:
>> Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management
>> Studio
>> '
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "Matt" <Matt@.discussions.microsoft.com> wrote in message
>> news:DD381736-D551-4BB6-9488-D2E61C97E33F@.microsoft.com...
>> > We have installed SQL Server 2005 on a machine running Windows 2003. We
>> > found
>> > out that there is no Enterprise Manager, but Management Studio. We have
>> > no
>> > idea how to run this.
>> >
>> > We tried to remove SQL Server, but Add/Remove doesn't remove it. Just
>> > only
>> > removes it from the list.
>> >
>> > We hope we can get an answer, because we are a serious evaluating SQL
>> > 2005
>> > over Oracle 10g.
>> >
>> > Thanks for your help.
>> >
>> > Matt
>>|||Thanks for your help, but I tried already all of that.
The bizarre thing is that it doesn't remove the installation through
Add/Remove Programs, while it takes it from the list. When you try to install
certain components (through Advanced) then it tells you it is already there.
Another problem is that when you install subcomponents it won't let you
install other subcomponents later.
This product definitely has installation issues and I hope they will be
resolved as the pack will now start evaluating (like us) and they're turned
away before they can even try away. A smooth installation is software
marketing 101.
Any help is appreciated. Even help to remove the product.
"Adam Machanic" wrote:
> Did you already go back through the installer and make sure that the correct
> options were selected? Also try switching into Advanced mode to be certain.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:5D45FE06-D501-4CC9-8DC0-3C786F6ECFEE@.microsoft.com...
> > We would assume that it was that simple, but in the Microsoft SQL Server
> > 2005
> > menu only Configurations Tools is shown.
> >
> > We have seen another post with the same problem but they were trying to
> > install it on XP, while we have installed it on Server 2003.
> >
> > The installation program seems to run so smooth, so we can't imagine that
> > something crucial like this was overlooked.
> >
> > Any help is appreciated.
> >
> > Matt
> >
> > "Adam Machanic" wrote:
> >
> >> Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management
> >> Studio
> >>
> >> '
> >>
> >> --
> >> Adam Machanic
> >> Pro SQL Server 2005, available now
> >> http://www.apress.com/book/bookDisplay.html?bID=457
> >> --
> >>
> >>
> >> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> >> news:DD381736-D551-4BB6-9488-D2E61C97E33F@.microsoft.com...
> >> > We have installed SQL Server 2005 on a machine running Windows 2003. We
> >> > found
> >> > out that there is no Enterprise Manager, but Management Studio. We have
> >> > no
> >> > idea how to run this.
> >> >
> >> > We tried to remove SQL Server, but Add/Remove doesn't remove it. Just
> >> > only
> >> > removes it from the list.
> >> >
> >> > We hope we can get an answer, because we are a serious evaluating SQL
> >> > 2005
> >> > over Oracle 10g.
> >> >
> >> > Thanks for your help.
> >> >
> >> > Matt
> >>
> >>
> >>
>
>

How to run SQL Server Management Studio

We have installed SQL Server 2005 on a machine running Windows 2003. We foun
d
out that there is no Enterprise Manager, but Management Studio. We have no
idea how to run this.
We tried to remove SQL Server, but Add/Remove doesn't remove it. Just only
removes it from the list.
We hope we can get an answer, because we are a serious evaluating SQL 2005
over Oracle 10g.
Thanks for your help.
MattStart -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management
Studio
'
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:DD381736-D551-4BB6-9488-D2E61C97E33F@.microsoft.com...
> We have installed SQL Server 2005 on a machine running Windows 2003. We
> found
> out that there is no Enterprise Manager, but Management Studio. We have no
> idea how to run this.
> We tried to remove SQL Server, but Add/Remove doesn't remove it. Just only
> removes it from the list.
> We hope we can get an answer, because we are a serious evaluating SQL 2005
> over Oracle 10g.
> Thanks for your help.
> Matt|||We would assume that it was that simple, but in the Microsoft SQL Server 200
5
menu only Configurations Tools is shown.
We have seen another post with the same problem but they were trying to
install it on XP, while we have installed it on Server 2003.
The installation program seems to run so smooth, so we can't imagine that
something crucial like this was overlooked.
Any help is appreciated.
Matt
"Adam Machanic" wrote:

> Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management
> Studio
> '
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:DD381736-D551-4BB6-9488-D2E61C97E33F@.microsoft.com...
>
>|||Did you already go back through the installer and make sure that the correct
options were selected? Also try switching into Advanced mode to be certain.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:5D45FE06-D501-4CC9-8DC0-3C786F6ECFEE@.microsoft.com...[vbcol=seagreen]
> We would assume that it was that simple, but in the Microsoft SQL Server
> 2005
> menu only Configurations Tools is shown.
> We have seen another post with the same problem but they were trying to
> install it on XP, while we have installed it on Server 2003.
> The installation program seems to run so smooth, so we can't imagine that
> something crucial like this was overlooked.
> Any help is appreciated.
> Matt
> "Adam Machanic" wrote:
>|||Thanks for your help, but I tried already all of that.
The bizarre thing is that it doesn't remove the installation through
Add/Remove Programs, while it takes it from the list. When you try to instal
l
certain components (through Advanced) then it tells you it is already there.
Another problem is that when you install subcomponents it won't let you
install other subcomponents later.
This product definitely has installation issues and I hope they will be
resolved as the pack will now start evaluating (like us) and they're turned
away before they can even try away. A smooth installation is software
marketing 101.
Any help is appreciated. Even help to remove the product.
"Adam Machanic" wrote:

> Did you already go back through the installer and make sure that the corre
ct
> options were selected? Also try switching into Advanced mode to be certai
n.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:5D45FE06-D501-4CC9-8DC0-3C786F6ECFEE@.microsoft.com...
>
>

How To Run SQL Script

Hi,

I have a SQL script which create few tables.
Currently, I have to paste the scripts to the SQL Enterprise Manager
and highlight one by one to run it. It can't seem to run it as "one
unit".

My script looks like below:

CREATE TABLE T1 AS ...;
CREATE TABLE T2 AS ...;

Any comments?

Thanks in advance.You can run them as a single unit, if these are just CREATE TABLE
statements. Are you getting any errors?

However if you are creating objects with cyclic dependencies you may have to
make sure the dependant objects must be created before the parent object.
One alternative to this is to use CREATE SCHEMA DDL & details can be found
in SQL Server Books Online.

--
- Anith
( Please reply to newsgroups only )

How to retrieve the name of the columns?

Hi everyone,

On daily basis I need to generate excerpts by mean of Excel. Prior to sql25k I used to play with Enterprise Manager and pick up the name of all the columns for a table very easily doing this

select top 1 f1,f2,-.. from table

nameage

enric80

How do I such thing from Sql Management Studio?

It's a silly thing, I know, but it's very useful for me because when I've got those columns then I can do paste them perfectly into .XLS.

Otherwise I see forced to write one by one and sometimes tables have more than 60 columns

It's not useful generate a CREATE TABLE script or launch SP_HELP <MYTABLE> because I obtain the name of the columns in vertical no horizontal.

Thanks a lot!!!

Hi Enric

How about this:

EXEC sp_columns @.table_name = 'customers'

Give it a try

Rgds,

Worf

|||select * from information_schema.columns
where table_name = 'customers'|||

Hi guys,

It doesn't useful at all for me. I'm looking for a way which allow me get the columns in horizontal way, just for copy them:

f1 f2 f3 f4

TIA

|||

I gotcha..you can do the same thing in Managment Studio. Just create a new query, and type in:

select top 1 * from Table

that will give you the fields that you want.

|||

hi there,

Ok, setting "results as text" in results panel.

Thanks

|||

Declare @.columns nvarchar(max)

Select @.columns = IsNull(@.columns + ', ', '') + column_name

From information_schema.columns

Where table_name = 'customers'

Select @.columns

-- or

Print @.columns

How to restrict access to database to only IUSR_<machinename>

Hello Mark,
Thanks for your message.
What do you mean " I removed TCP/IP and Named Pipes from the SQL Server
Registration from within Enterprise Manager and rebooted the machine."? Do
you mean you have performed the following steps?
1. Right-click 'MYSERVER\SERVER' in SQL Enterprise Manager(SEM), click
Properties.
2. Click General tab in the Properties window, click Network Configuration.
3. Remove TCP/IP and Named Pipes from the "SQL Server Network Utility."
Please let me know what steps you performed.
If SQL Server service didn't start, SQL Server Agent service will not be
started. Please make sure the SQL Server service has been started. You can
follow the steps below to start SQL Server service:
Click Start > All Programs > SQL Server > Service Manager > start SQL
Server service in Service Manager > start SQL Server Agent service in
Service Manager
If you still are unable to start the SQL Server Agent service, please
follow the steps below to attempt to start the SQL Server Agent Services
from a command line, then send the error log files to me.
1. Open a command line window.
2. Run the following command in the command line window:
"C:\Program Files\Microsoft SQL Server\<instance name>\Binn\sqlagent" -c -v
Note: Above command is just a sample. You need to replace the directory
"C:\Program Files\Microsoft SQL Server\<instance name>\ with the real
directory which you installed the SQL Server.
3. Compress all the log files under the directory "C:\Program
Files\Microsoft SQL Server\<instance name>\LOG" and send it to me for
research.
In addition, you can open "SQL Server Network Utility." by clicking Start >
All Programs > SQL server > SQL Server Network Utility, then you can
re-enable TCP/IP and Named Pipes in the SQL Server Network Utility.
If anything is unclear, get in touch.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.>>Do you mean you have performed the following steps?
Yes, that is correct. When I did that, and rebooted the machine, the
SQLServerAgent would not start. (It would start, then immediately stop).
Therefore I could not open my database using Enterprise Manager.
NOTE: I had also set the 'Hide Server' switch, and suspect this may have
been part of the problem as well.
SOLUTION: I went into the registry under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\MIRACLECAT\MSSQLServer\SuperSocke
tNetLib\Tcp and set the TcpHideFlag
back to zero and then rebooted and all worked again.
I think I may just depend on Windows Server 2003 blocking post 1433 (SQL
Server Port) and let it go at that since temporarily losing my SQL databases
put quite a scare into me
Thanks for all your help however.
Mark
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:HCsqCPf%23EHA.3360@.cpmsftngxa10.phx.gbl...
> Hello Mark,
> Thanks for your message.
> What do you mean " I removed TCP/IP and Named Pipes from the SQL Server
> Registration from within Enterprise Manager and rebooted the machine."?
> Do
> you mean you have performed the following steps?
> 1. Right-click 'MYSERVER\SERVER' in SQL Enterprise Manager(SEM), click
> Properties.
> 2. Click General tab in the Properties window, click Network
> Configuration.
> 3. Remove TCP/IP and Named Pipes from the "SQL Server Network Utility."
> Please let me know what steps you performed.
> If SQL Server service didn't start, SQL Server Agent service will not be
> started. Please make sure the SQL Server service has been started. You can
> follow the steps below to start SQL Server service:
> Click Start > All Programs > SQL Server > Service Manager > start SQL
> Server service in Service Manager > start SQL Server Agent service in
> Service Manager
> If you still are unable to start the SQL Server Agent service, please
> follow the steps below to attempt to start the SQL Server Agent Services
> from a command line, then send the error log files to me.
> 1. Open a command line window.
> 2. Run the following command in the command line window:
> "C:\Program Files\Microsoft SQL Server\<instance
> name>\Binn\sqlagent" -c -v
> Note: Above command is just a sample. You need to replace the directory
> "C:\Program Files\Microsoft SQL Server\<instance name>\ with the real
> directory which you installed the SQL Server.
> 3. Compress all the log files under the directory "C:\Program
> Files\Microsoft SQL Server\<instance name>\LOG" and send it to me for
> research.
>
> In addition, you can open "SQL Server Network Utility." by clicking Start
> All Programs > SQL server > SQL Server Network Utility, then you can
> re-enable TCP/IP and Named Pipes in the SQL Server Network Utility.
> If anything is unclear, get in touch.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

How to restrict access our DB on SQL server.

Hi,
How can we restrict our DB if connecting thru other applications like SQL query analyzer, Excel & even Enterprise manager?
but this DB should be accessible by our application (from front end) only...
I learned about "Application Role" in SQL server 2000 but to apply this
we will have to change our code thousand places.
Have any idea...Pl. do suggest?
Thanx in advance!!!!
Regards,
Paresh...
Message posted via http://www.sqlmonster.com
Don't give out the passwords and they won't be able to login. If they have
a login and password then they can get in and do what ever the current
permissions allow them to.
Andrew J. Kelly SQL MVP
"paresh goyal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d15479cc26b24cf0a782a131acead60a@.SQLMonster.c om...
> Hi,
> How can we restrict our DB if connecting thru other applications like SQL
> query analyzer, Excel & even Enterprise manager?
> but this DB should be accessible by our application (from front end)
> only...
> I learned about "Application Role" in SQL server 2000 but to apply this
> we will have to change our code thousand places.
> Have any idea...Pl. do suggest?
> Thanx in advance!!!!
> Regards,
> Paresh...
> --
> Message posted via http://www.sqlmonster.com
|||Sounds like you're looking for a solution that might not exist. If your
users that currently use the applications login to SQL with their own
userids, they can use the same userids with any query tool and do whatever
they have permissions for. You could try to use different ports, server
aliases, etc... to make it tougher for them to discover where the actual
sql db sits but it's just a speed bump, not a barrier.
aK.
"paresh goyal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d15479cc26b24cf0a782a131acead60a@.SQLMonster.c om...
> Hi,
> How can we restrict our DB if connecting thru other applications like SQL
query analyzer, Excel & even Enterprise manager?
> but this DB should be accessible by our application (from front end)
only...
> I learned about "Application Role" in SQL server 2000 but to apply this
> we will have to change our code thousand places.
> Have any idea...Pl. do suggest?
> Thanx in advance!!!!
> Regards,
> Paresh...
> --
> Message posted via http://www.sqlmonster.com