How to se TempTables dat in Query Analazer

Hi all,
i am very interested on sqlprogramming, i want to see the #temptables
data in Query analazer, and i want to create a log files that how many
#tables created at instalation time , later i want to display the data
in form or grid, any one can please help me..
Thanks in advance
SureshSuresh wrote:
> Hi all,
> i am very interested on sqlprogramming, i want to see the #temptables
> data in Query analazer, and i want to create a log files that how many
> #tables created at instalation time , later i want to display the data
> in form or grid, any one can please help me..
>
> Thanks in advance
> Suresh
>
I'm not sure that I fully understand what you are looking for but if you
want to view data in a temp table you just run a select like on every
other regular table - e.g. SELECT column1, column2... FROM #YourTempTable
Regards
Steen Schlter Persson
DBA|||Read up on temp tables within Books Online.
If you create a temp table (denoted by a single # sign) you can query the
data within the table ONLY if you query it on the same connection that
created the table (and the thing that created the table was not a stored
procedure that you just ececuted).
I am not sure what you mean when you say you want to see how many # tables
were created "at instalation time." Installation of what?
Keith Kratochvil
"Suresh" <suresh_yalla@.hotmail.com> wrote in message
news:1149678535.349104.296080@.y43g2000cwc.googlegroups.com...
> Hi all,
> i am very interested on sqlprogramming, i want to see the #temptables
> data in Query analazer, and i want to create a log files that how many
> #tables created at instalation time , later i want to display the data
> in form or grid, any one can please help me..
>
> Thanks in advance
> Suresh
>

How to se TempTables dat in Query Analazer

Hi all,
i am very interested on sqlprogramming, i want to see the #temptables
data in Query analazer, and i want to create a log files that how many
#tables created at instalation time , later i want to display the data
in form or grid, any one can please help me..
Thanks in advance
SureshSuresh wrote:
> Hi all,
> i am very interested on sqlprogramming, i want to see the #temptables
> data in Query analazer, and i want to create a log files that how many
> #tables created at instalation time , later i want to display the data
> in form or grid, any one can please help me..
>
> Thanks in advance
> Suresh
>
I'm not sure that I fully understand what you are looking for but if you
want to view data in a temp table you just run a select like on every
other regular table - e.g. SELECT column1, column2... FROM #YourTempTable
--
Regards
Steen Schlüter Persson
DBA|||Read up on temp tables within Books Online.
If you create a temp table (denoted by a single # sign) you can query the
data within the table ONLY if you query it on the same connection that
created the table (and the thing that created the table was not a stored
procedure that you just ececuted).
I am not sure what you mean when you say you want to see how many # tables
were created "at instalation time." Installation of what?
--
Keith Kratochvil
"Suresh" <suresh_yalla@.hotmail.com> wrote in message
news:1149678535.349104.296080@.y43g2000cwc.googlegroups.com...
> Hi all,
> i am very interested on sqlprogramming, i want to see the #temptables
> data in Query analazer, and i want to create a log files that how many
> #tables created at instalation time , later i want to display the data
> in form or grid, any one can please help me..
>
> Thanks in advance
> Suresh
>

How to script Sprocs permissions in Management Studio?

I am dissapointed that script options are missing in Management Studio. I
never understand why a good feature goes away in a newer version.
Is there any way to script the permissions for stored procedures?
John Dalberg
Depends on how you script them. If you select the stored
procedure and select "Script Stored Procedure" you won't get
the permissions. If you go from the database level, Tasks
and Generate SQL Scripts (or whatever other ways there are
to invoke the Generate SQL Server Scripts Wizard, you have
more options in the scripting. If you script the stored
procedures for the database and select to include object
level permissions, the grants will be scripted with the
stored procedures.
-Sue
On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
Dalberg) wrote:

>I am dissapointed that script options are missing in Management Studio. I
>never understand why a good feature goes away in a newer version.
>Is there any way to script the permissions for stored procedures?
>John Dalberg
|||Sue Hoegemeier <Sue_H@.nomail.please> wrote:
> Depends on how you script them. If you select the stored
> procedure and select "Script Stored Procedure" you won't get
> the permissions. If you go from the database level, Tasks
> and Generate SQL Scripts (or whatever other ways there are
> to invoke the Generate SQL Server Scripts Wizard, you have
> more options in the scripting. If you script the stored
> procedures for the database and select to include object
> level permissions, the grants will be scripted with the
> stored procedures.
Not in SQL Server 2005. I found out later that it can be done at the
database level. Still not as convenient as pre 2005.
John Dalberg
[vbcol=seagreen]
> On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
> Dalberg) wrote:
|||Yes...what I wrote was for SQL Server 2005. And I explained
how to do it at the database level in SQL Server 2005.
That's what I wrote from what I can see..."If you go from
the database level..."
-Sue
On 13 Mar 2006 15:36:47 GMT, nospam@.nospam.sss (John
Dalberg) wrote:
[vbcol=seagreen]
>Sue Hoegemeier <Sue_H@.nomail.please> wrote:
>
>Not in SQL Server 2005. I found out later that it can be done at the
>database level. Still not as convenient as pre 2005.
>John Dalberg
>
>
>

How to script Sprocs permissions in Management Studio?

I am dissapointed that script options are missing in Management Studio. I
never understand why a good feature goes away in a newer version.
Is there any way to script the permissions for stored procedures?
John DalbergDepends on how you script them. If you select the stored
procedure and select "Script Stored Procedure" you won't get
the permissions. If you go from the database level, Tasks
and Generate SQL Scripts (or whatever other ways there are
to invoke the Generate SQL Server Scripts Wizard, you have
more options in the scripting. If you script the stored
procedures for the database and select to include object
level permissions, the grants will be scripted with the
stored procedures.
-Sue
On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
Dalberg) wrote:
>I am dissapointed that script options are missing in Management Studio. I
>never understand why a good feature goes away in a newer version.
>Is there any way to script the permissions for stored procedures?
>John Dalberg|||Sue Hoegemeier <Sue_H@.nomail.please> wrote:
> Depends on how you script them. If you select the stored
> procedure and select "Script Stored Procedure" you won't get
> the permissions. If you go from the database level, Tasks
> and Generate SQL Scripts (or whatever other ways there are
> to invoke the Generate SQL Server Scripts Wizard, you have
> more options in the scripting. If you script the stored
> procedures for the database and select to include object
> level permissions, the grants will be scripted with the
> stored procedures.
Not in SQL Server 2005. I found out later that it can be done at the
database level. Still not as convenient as pre 2005.
John Dalberg
> On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
> Dalberg) wrote:
> >I am dissapointed that script options are missing in Management Studio.
> >I never understand why a good feature goes away in a newer version.
> >
> >Is there any way to script the permissions for stored procedures?
> >
> >John Dalberg|||Yes...what I wrote was for SQL Server 2005. And I explained
how to do it at the database level in SQL Server 2005.
That's what I wrote from what I can see..."If you go from
the database level..."
-Sue
On 13 Mar 2006 15:36:47 GMT, nospam@.nospam.sss (John
Dalberg) wrote:
>Sue Hoegemeier <Sue_H@.nomail.please> wrote:
>> Depends on how you script them. If you select the stored
>> procedure and select "Script Stored Procedure" you won't get
>> the permissions. If you go from the database level, Tasks
>> and Generate SQL Scripts (or whatever other ways there are
>> to invoke the Generate SQL Server Scripts Wizard, you have
>> more options in the scripting. If you script the stored
>> procedures for the database and select to include object
>> level permissions, the grants will be scripted with the
>> stored procedures.
>
>Not in SQL Server 2005. I found out later that it can be done at the
>database level. Still not as convenient as pre 2005.
>John Dalberg
>
>
>
>> On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
>> Dalberg) wrote:
>> >I am dissapointed that script options are missing in Management Studio.
>> >I never understand why a good feature goes away in a newer version.
>> >
>> >Is there any way to script the permissions for stored procedures?
>> >
>> >John Dalberg

How to script Sprocs permissions in Management Studio?

I am dissapointed that script options are missing in Management Studio. I
never understand why a good feature goes away in a newer version.
Is there any way to script the permissions for stored procedures?
John DalbergDepends on how you script them. If you select the stored
procedure and select "Script Stored Procedure" you won't get
the permissions. If you go from the database level, Tasks
and Generate SQL Scripts (or whatever other ways there are
to invoke the Generate SQL Server Scripts Wizard, you have
more options in the scripting. If you script the stored
procedures for the database and select to include object
level permissions, the grants will be scripted with the
stored procedures.
-Sue
On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
Dalberg) wrote:

>I am dissapointed that script options are missing in Management Studio. I
>never understand why a good feature goes away in a newer version.
>Is there any way to script the permissions for stored procedures?
>John Dalberg|||Sue Hoegemeier <Sue_H@.nomail.please> wrote:
> Depends on how you script them. If you select the stored
> procedure and select "Script Stored Procedure" you won't get
> the permissions. If you go from the database level, Tasks
> and Generate SQL Scripts (or whatever other ways there are
> to invoke the Generate SQL Server Scripts Wizard, you have
> more options in the scripting. If you script the stored
> procedures for the database and select to include object
> level permissions, the grants will be scripted with the
> stored procedures.
Not in SQL Server 2005. I found out later that it can be done at the
database level. Still not as convenient as pre 2005.
John Dalberg
[vbcol=seagreen]
> On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
> Dalberg) wrote:
>|||Yes...what I wrote was for SQL Server 2005. And I explained
how to do it at the database level in SQL Server 2005.
That's what I wrote from what I can see..."If you go from
the database level..."
-Sue
On 13 Mar 2006 15:36:47 GMT, nospam@.nospam.sss (John
Dalberg) wrote:
[vbcol=seagreen]
>Sue Hoegemeier <Sue_H@.nomail.please> wrote:
>
>Not in SQL Server 2005. I found out later that it can be done at the
>database level. Still not as convenient as pre 2005.
>John Dalberg
>
>
>
>sql

how to script security permissions?

I have a very large table that is refreshed periodically. Since it's
so big, I do a 'drop table', 'create table', 'create index' then a bulk
load. It's much faster than doing a 'delete from'. I also do a
'shrinkdb' as part of this process.

The problem, however, is that the user permissions are also dropped in
this process. So, how can I script the user permissions? For example,
how do I give 'MyUser' select access to 'MyTable' in 'MyDB'? Many
thanks!!

Eben Yong
yonglove@.yahoo.comGRANT SELECT ON MyTable TO MyUser|||Eben (yonglove@.yahoo.com) writes:
> I have a very large table that is refreshed periodically. Since it's
> so big, I do a 'drop table', 'create table', 'create index' then a bulk
> load. It's much faster than doing a 'delete from'. I also do a
> 'shrinkdb' as part of this process.

So why not do a TRUNCATE TABLE instead? This is a minimally logged
operation, and you maintain indexes, permissions etc.

> The problem, however, is that the user permissions are also dropped in
> this process. So, how can I script the user permissions? For example,
> how do I give 'MyUser' select access to 'MyTable' in 'MyDB'? Many
> thanks!!

I guess you can do this with DMO, if you want to do this programmatically.
However, I have not used DMO myself.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Eben (yonglove@.yahoo.com) writes:
> > I have a very large table that is refreshed periodically. Since it's
> > so big, I do a 'drop table', 'create table', 'create index' then a bulk
> > load. It's much faster than doing a 'delete from'. I also do a
> > 'shrinkdb' as part of this process.
> So why not do a TRUNCATE TABLE instead? This is a minimally logged
> operation, and you maintain indexes, permissions etc.
Although keeping indexes is sometimes a good thing, it's also sometimes
beneficial to drop all indexes before the truncate and only add them
back when all the data loading is complete.

Of course, it depends on how the data loading is being managed (in my
case, I happen to be loading 3 1/2 million records from a non-R DBMS,
and having to do it one row at a time - I'd rather not have the indexes
rebuilt for every insert)

Damien|||Thank you, everyone. I did not know about the TRUNCATE TABLE option.
But accomplishing this objective using DROP TABLE, CREATE TABLE, and so
on, has required that I learn many other SQL Server methods, so it's
good for me. I spent more time developing the solution but SQL Server
doesn't care one way or the other and both methods still get the job
done in the same amount of time. So, once again, thanks everyone for
your input!

How to script Role Memebers

Is there any way to script Databse Role(s) and Role Memeber(s) using SQL-SMO?

The workaround i am using here is, set the server connection execution mode to CaptureSQL and and use AddMemeber method while looping through Server.Database.Roles.EnumMemebers. This creates a string collection of sp_addrolemembers. (let me know if someone wants to have a look at this code)

I couldn't even figureout how to do this using SQL Server Management Studion. When i script the entire database using SQL Server Mangement Studio, i dont see sp_addrolemember statements.

Am I missing something?

Try something like this:

Dim scrDBScript As Scripter
Dim objSMOObjects(100) As SqlSmoObject
Dim intObjCount As Integer
intObjCount = 0

Dim srv As Server
Dim srvConn As ServerConnection
srv = New Server("MyServer")
srvConn = srv.ConnectionContext
srvConn.LoginSecure = True

Dim db As Database
Dim objRoles As DatabaseRoleCollection
Dim objRole As DatabaseRole

db = srv.Databases("AdventureWorks")
objRoles = db.Roles
For Each objRole In objRoles
objSMOObjects(intObjCount) = objRole
intObjCount += 1
Next

scrDBScript = New Scripter(srv)
scrDBScript.Options.FileName = "c:\DBScript.sql"
scrDBScript.Options.IncludeHeaders = True
scrDBScript.Options.AppendToFile = True

ReDim Preserve objSMOObjects(intObjCount - 1)
scrDBScript.Script(objSMOObjects)

This code will create a script of each role defined in the AdventureWorks database. Hope that helps.

|||

Allen, Thanks for replying on this! However, this code only genrates sp_addrole statements. What I am looking for is to script rolememebers (sp_addrolememeber).

Any thoughts?

|||

Not sure if this works, but if it doesn't I think you'll get the idea:

db = srv.Databases("AdventureWorks")
objRoles = db.Roles
For Each objRole In objRoles
Dim colMembers As System.Collections.Specialized.StringCollection
Dim strMember As String
colMembers = objRole.EnumMembers
For Each strMember In colMembers
objSMOObjects(intObjCount) = objRole
intObjCount += 1
Next
Next

The EnumMembers method of the DatabaseRole object returns a string collection with the members of the role in the collection.