restrict access to database: database

Showing posts with label database. Show all posts

Friday, March 30, 2012

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 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?

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.

How to script permissions?

I'm using SQL Server 2000. I have an application that uses three
databases. Currently, when a new user is added (Windows Integration),
I go to each database, create the login then go to each spoc and give
that user rights.
Is there a faster more efficient way to do this? For example, I have
maybe two or three scripts (for various permission levels) that I can
drop in a user name and it does everything?
Thanks,
BrettYou could probably use the GRANT statement and dynamic SQL to do the trick.
"brett" <account@.cygen.com> wrote in message
news:1146001887.339002.206390@.g10g2000cwb.googlegroups.com...
> I'm using SQL Server 2000. I have an application that uses three
> databases. Currently, when a new user is added (Windows Integration),
> I go to each database, create the login then go to each spoc and give
> that user rights.
> Is there a faster more efficient way to do this? For example, I have
> maybe two or three scripts (for various permission levels) that I can
> drop in a user name and it does everything?
> Thanks,
> Brett
>|||Or, add the user to a Windows Group, and give that Group permissions.|||The Windows Group sounds like a good solution.
To be more automated, I'd like to build this process into application
deployment. Are there any spocs I can run to setup a new user login
and then start assigned specific permissions to objects in a database?
Thanks,
Brett|||If you want the user to have execute permission to every proc you could
use this
--Grab all the procedures for the current DB
SELECT IDENTITY(INT,1,1) AS ID,
SPECIFIC_NAME
INTO #Procedurelist
FROM INFORMATION_SCHEMA.ROUTINES --Only Procs
WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),
'IsMSShipped') =0
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY SPECIFIC_NAME
DECLARE
@.Loopid INT,
@.MaxId INT,
@.UserName VARCHAR(50)
--This is the user that will get the execute permissions
SELECT @.UserName = 'SomeUser'
--Grab start and end values for the loop
SELECT @.Loopid = 1,
@.MaxId = MAX(ID)
FROM #Procedurelist
DECLARE
@.SQL VARCHAR(500),
@.ProcName VARCHAR(400)
--This is where the loop starts
WHILE @.Loopid <= @.MaxId BEGIN
--grab the procedure name
SELECT @.ProcName = SPECIFIC_NAME
FROM #Procedurelist
WHERE ID = @.Loopid
--construct the statement
SELECT @.SQL = 'GRANT EXECUTE ON ' + @.ProcName + ' TO ' + @.UserName
PRINT (@.SQL) --change PRINT to EXECUTE if you want it to run
automatically
--increment counter
SET @.Loopid = @.Loopid + 1
END
--clean up
DROP TABLE #Procedurelist
if you need user defined functions also use this
http://sqlservercode.blogspot.com/2...or.html

Denis the SQL Menace
http://sqlservercode.blogspot.com/

How to script out my .mdf database I have in Visual Studio?

I'm using Visual Studio 2005 Standard. I have an .mdf database inside my Visual Studio "Server Explorer" under "Data Connections."

How do I script out this database, including the stored procedures in it? In other words, I want to export a SQL script that will create this database.

I'm very much a newbie when it comes to working with database management. I thought I created this .mdf inside my local install of SQL Server 2005 Express, but maybe I did it somehow inside Visual Studio in the Server Explorer. When I open up SQL Server Management Studio Express I don't see this database. I did locate the .mdf on my local C drive but I haven't been able to get Mgt Studio to open it.

Hi hapax_legomenon,

I use the Database Publishing Wizard to script my databases. You can select schema, data, schema and data, and be selective about which database objects you want and don't want. Microsoft wrote it, it's free, and on my list of "must-have" utilities. Here's the URL:

http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

Joe

|||

That's a great tool. Thanks!

how to script out .mdf file to .sql?

I am using Sqlserver2005. I need to convert .mdf to sql script. how can I do this?

Thanks

In your SQL management studio you right click on the database node and select "Tasks-->Generate Scripts..." This will start a wizard for you.

|||

Tasks->Generate Scripts will only generate a script to re-create the database. It will not create any table, let alone the table data.

Download the Microsoft SQL Server Database Publishing Wizard. This will script the entire database for you.

Jos

|||

Jos Branders:
Download the Microsoft SQL Server Database Publishing Wizard. This will script the entire database for you.

Hi Jos,

Through wizard it asks for database name. But I don't have any database created on SQLServer Management Studio. I just have .mdf and .ldf files.

How can I create sqlscript using those files?

Thanks

|||

You have to first import the database in sql server. Right click on databases and select Attach database.

|||

Thank you Richard. Its working.

sql

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 Script Jobs In SQL Server ?

How to create SQL script out of job in SQL Server ?
I know there is a way thru EM, but is there any stored procedure or any function that will return the SQL script of any job.
I want to script all jobs on my server just as a part backing up mechanism.
Thanks
Decasto.After starting Profiler, going into EM and scripting a job I noticed two procs being called...

msdb..sp_help_jobstep @.job_id = XXX
and
msdb..sp_help_jobschedule @.job_id = XXX

soooo I suppose you could step through msdb..sysjobs and process each job you find with one or both of the above procs.

How to script index in 2005

sql 2005
right click on table to script - create script does not include indexes, or
perms. how to include?
any help appreciated
thanks
chris
Hi,
use the script wizard and enable the option "Include Script Indexes".
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Jens
> use the script wizard and enable the option "Include Script Indexes".
I thought it will be able in SP2 comming soon,isn't it?
If it isn't can yoi point out?
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1168553540.198986.94110@.77g2000hsv.googlegrou ps.com...
> Hi,
> use the script wizard and enable the option "Include Script Indexes".
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

How to script index in 2005

sql 2005
right click on table to script - create script does not include indexes, or
perms. how to include?
any help appreciated
thanks
chrisHi,
use the script wizard and enable the option "Include Script Indexes".
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Jens
> use the script wizard and enable the option "Include Script Indexes".
I thought it will be able in SP2 comming soon,isn't it?
If it isn't can yoi point out?
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1168553540.198986.94110@.77g2000hsv.googlegroups.com...
> Hi,
> use the script wizard and enable the option "Include Script Indexes".
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

How to script existing database role WITH securables?

Hello!

Please, help with the subject. SMO generates only this:

USE [DB1]

/****** Object: DatabaseRole [VIP_RDR] Script Date: 05/28/2007 03:48:05 ******/

CREATE ROLE [VIP_RDR] AUTHORIZATION [dbo]

Securables are not included for some reason.

Thank you.

Well, SMO does not do that.

So - "manually", using GRANT (together with select from sys.objects if you can distinct database objects using select clause)

Good luck!

sql

How to script existing database role WITH securables?

Hello!

Please, help with the subject. SMO generates only this:

USE [DB1]

/****** Object: DatabaseRole [VIP_RDR] Script Date: 05/28/2007 03:48:05 ******/

CREATEROLE [VIP_RDR] AUTHORIZATION [dbo]

Securables are not included for some reason.

Thank you.

Well, SMO does not do that.

So - "manually", using GRANT (together with select from sys.objects if you can distinct database objects using select clause)

Good luck!

How to script all sql jobs

Hello,

Is there a way in sql2k5 to script all sql jobs at once. In sql2k you could right click on the agent and select that option however I do not see it in sql2k5. Thanks.

Hi John. Click the 'Jobs' folder under the SQL Server Agent node in SSMS, then hit the F7 key (brings up the Summary pane). Highlight all the jobs you want to script using a combination of Shift and Ctrl keys, then right click, Script Job as..., then choose where to script to.

HTH,

|||

Chad,

That works. Thanks.

How to script all Publications using SMO in 2005

How to script all Publications using SMO in 2005
Hi Paul / All,
I have used your script from you site www.replicationanswers.com to
script all publications in SQL 2000 . Work great thanks !!.
I am now testing SQL server 2005 – what is the way to work with SMO in
order to script all objects ?
Thanks in advance,
Eyal
Right click on replication node in the SQL Server Management Studio and
select Generate Scripts. Would that help?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"? ??" <nospameyalSchapira@.hotmail.com> wrote in message
news:F3E1C68F-68BC-4A17-B9C6-2A4FC7D18304@.microsoft.com...
> How to script all Publications using SMO in 2005
> Hi Paul / All,
> I have used your script from you site www.replicationanswers.com to
> script all publications in SQL 2000 . Work great thanks !!.
>
> I am now testing SQL server 2005 - what is the way to work with SMO in
> order to script all objects ?
> Thanks in advance,
> Eyal
>
|||Hi have to do it with SMO once a week triggers it in a job into a text file.
sql

How to script adding a field to a table

Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***Do it in EM, and press "Save Change Script" button before exiting the window
s, and you will get the
script served on a silver plate (almost).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl..
.
> Hi - I would like to know what sql to run in Query Analyzer to add two
> fields to an existing table (I know you can do this in Enterprise
> Manager - but I'd like to be able to send a script to someone to let it
> happen automatically).
> Column Name: IDCreated
> DataType: DateTime
> Length: 8
> Allow Nulls: False
> Default Value: (getdate())
> and
> Column Name: ChangeNum
> DataType: bigint
> Length: 8
> Allow Nulls: False
> Identity: Yes
> Identity Seed: 45
> Identity Increment: 1
> Thanks for any help,
> Mark
> *** Sent via Developersdex http://www.examnotes.net ***|||Here ya go:
create table MyTable
(
PK int primary key
)
go
alter table MyTable
add
IDCreated datetime not null
constraint DF1_Myatble default (getdate())
, ChangeNum bigint not null identity (45, 1)
go
drop table MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mark" <anonymous@.devdex.com> wrote in message
news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl...
Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***|||Thinking about it, go with Tom's suggestion. EM often does these things in a
less than optimal way.
Often you see EM creating a new table, copy data etc etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:%23xDANtCyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Do it in EM, and press "Save Change Script" button before exiting the wind
ows, and you will get
> the script served on a silver plate (almost).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MS
FTNGP12.phx.gbl...
>|||Like this:
ALTER TABLE table_name ADD idcreated DATETIME NOT NULL
CONSTRAINT df_table_name_idcreated
DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE table_name ADD changenum BIGINT NOT NULL
IDENTITY(45,1) ;
Usually, when you add an IDENTITY column you will want to add a unique
or primary key constraint on that column. Although that's not
mandatory, IDENTITY itself won't prevent duplicates in all
circumstances because the auto-generated value can be overridden or the
seed can be changed. Also, IDENTITY is typically referenced by a
foreign key, for which a constraint is required.
Depending on your requirements you can add a constraint like this:
ALTER TABLE table_name
ADD CONSTRAINT ak_table_name_change_num UNIQUE (changenum) ;
David Portas
SQL Server MVP
--|||> Often you see EM creating a new table, copy data etc etc.
and sometimes it is the optimal way, is it not?

How To Script

I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the table
name is dbo.item. What would my script look like to do this in SQL server
2005? The column has a silver key by it too in SQL Server Management
Studio.

ThanksBrian (b.houghtby@.eaglecrusher.com) writes:

Quote:

Originally Posted by

I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the
table name is dbo.item. What would my script look like to do this in
SQL server 2005? The column has a silver key by it too in SQL Server
Management Studio.

UPDATE dbo.item
SET IMA_GLInvAcctNbr = 11500
WHERE IMA_GLInvAcctNbr = 11283

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What does the silver key mean?

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9947EFB6764B9Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Brian (b.houghtby@.eaglecrusher.com) writes:

Quote:

Originally Posted by

>I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the
>table name is dbo.item. What would my script look like to do this in
>SQL server 2005? The column has a silver key by it too in SQL Server
>Management Studio.

>
UPDATE dbo.item
SET IMA_GLInvAcctNbr = 11500
WHERE IMA_GLInvAcctNbr = 11283
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||What does the silver key mean?

It means that it is in a foreign key relationship to another table.

On Jun 7, 9:51 am, "Brian" <b.hough...@.eaglecrusher.comwrote:

Quote:

Originally Posted by

>
"Erland Sommarskog" <esq...@.sommarskog.sewrote in message
>
news:Xns9947EFB6764B9Yazorman@.127.0.0.1...
>
>
>

Quote:

Originally Posted by

Brian (b.hough...@.eaglecrusher.com) writes:

Quote:

Originally Posted by

I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the
table name is dbo.item. What would my script look like to do this in
SQL server 2005? The column has a silver key by it too in SQL Server
Management Studio.

Quote:

Originally Posted by

UPDATE dbo.item
SET IMA_GLInvAcctNbr = 11500
WHERE IMA_GLInvAcctNbr = 11283

Quote:

Originally Posted by

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

Quote:

Originally Posted by

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...downloads/books...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ons/books.mspx- Hide quoted text -

>
- Show quoted text -

Friday, March 30, 2012

How to se TempTables dat in Query Analazer

How to se TempTables dat in Query Analazer

How to script Sprocs permissions in Management Studio?

How to script Sprocs permissions in Management Studio?

How to script Sprocs permissions in Management Studio?

how to script security permissions?

How to script Role Memebers

How to script permissions?

How to script out my .mdf database I have in Visual Studio?

how to script out .mdf file to .sql?

How to Script Muplitple SPROCS/VIEWS at one time

How To Script Jobs In SQL Server ?

How to script index in 2005

How to script index in 2005

How to script existing database role WITH securables?

How to script existing database role WITH securables?

How to script all sql jobs

How to script all Publications using SMO in 2005

How to script adding a field to a table

How To Script

restrict access to database

Blog Archive

About Me