Hi there:
I write scripts which I must execute in multiple environments (Dev,
Test, UAT, Prod). Dev and Test are located on the same server, so
it's easy to change the db with the USE command. UAT and Prod are on
another server. If I close the script and re-open it, I'm not
prompted for connection information -- it simply defaults to whichever
connection I used last (because I haven't closed SSMS?).
So far, I've use the Object Browser to navigate to an object within
the server.db I want, click New Query, then copy & paste the query
into the new window. This seems pretty cheesy. Is there a more
elegant approach?
Thanks;
Duncan
Duncan A. McRae (google.com@.mcrae.ca) writes:
> I write scripts which I must execute in multiple environments (Dev,
> Test, UAT, Prod). Dev and Test are located on the same server, so
> it's easy to change the db with the USE command. UAT and Prod are on
> another server. If I close the script and re-open it, I'm not
> prompted for connection information -- it simply defaults to whichever
> connection I used last (because I haven't closed SSMS?).
> So far, I've use the Object Browser to navigate to an object within
> the server.db I want, click New Query, then copy & paste the query
> into the new window. This seems pretty cheesy. Is there a more
> elegant approach?
Yes. Right-click in the query window and select Change Connection from
the context menu.
Overall, I prefer Query Analyzer over Mgmt Studio, but being able to
change the connection for a query window is a great feature.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Showing posts with label script. Show all posts
Showing posts with label script. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Changing the DataSource at Runtime
I have to create a script for changing the datasource at runtime.
Here is my screnario, While development I am using Data source name called "DevDatasource1" and when I am deploying it to other evnironment the datasource name will change let us say "QADatasource".
I have to create a script for changing the datasource(i.e. DevDatasource1 to QADatasource). How I can achieve I this using the setItemdatasource?
You can also set a parameter in your report to accept the specific database or source and use this parameter within your datasource connection string.
Within your report:
My connection string looks like this: ="Data Source=nt33;Initial Catalog=" & Parameters!Database.Value & ";"
This allows me to change the database on the fly, but could change the entire connection string including server.
Location for the code would be Data Tab --> Dataset properties --> Datasource editor --> Connection String:
The bonus is that no matter where the report is run you have the ability to change the datasource.
My parameter is setup to load al list of available databases for the user.
Not what you were looking for specifcally, but it might help.
|||hi simles
thanks for your reply.
Here is my problem. While deploying my reports on the other server we don't want to create the datasource. Already in the deployment server datasources are created.
what we have to do his while deploying the reports we have to map the existing datasources to the deploying rdl files. Can you explain me how we can achieve this using reports scripts.
|||So, you have a pre-existing datasource on the server, and you want your newly-published report to use IT instead of the datasource the report was created with, right?
If so, try SetItemDataSources():
http://msdn2.microsoft.com/ru-ru/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.setitemdatasources.aspx
|||Russell,
Thanks for your reply.
I tried with that setitemdatasources, but it not setting the new datasource name for the rdl.
I don't know were i am making the mistake. please look into the code and correct me if the code is wrong.
Dim dataSources() As DataSource
dataSources = rs.GetItemDataSources("/Reports/Test Matrix")
Dim NewdataSources As new DataSource
Dim dsref As New DataSourceReference
dsref.Reference = "/Data Sources/TestDatasource"
NewdataSources.Item = CType(dsref,DataSourceDefinitionOrReference)
NewdataSources.Name = "NewDatasource"
dataSources(0) = NewdataSources
rs.SetItemDataSources("/Reports/Test Matrix", dataSources)
Simran Raj
Changing the DataSource at Runtime
I have to create a script for changing the datasource at runtime.
Here is my screnario, While development I am using Data source name called "DevDatasource1" and when I am deploying it to other evnironment the datasource name will change let us say "QADatasource".
I have to create a script for changing the datasource(i.e. DevDatasource1 to QADatasource). How I can achieve I this using the setItemdatasource?
You can also set a parameter in your report to accept the specific database or source and use this parameter within your datasource connection string.
Within your report:
My connection string looks like this: ="Data Source=nt33;Initial Catalog=" & Parameters!Database.Value & ";"
This allows me to change the database on the fly, but could change the entire connection string including server.
Location for the code would be Data Tab --> Dataset properties --> Datasource editor --> Connection String:
The bonus is that no matter where the report is run you have the ability to change the datasource.
My parameter is setup to load al list of available databases for the user.
Not what you were looking for specifcally, but it might help.
|||hi simles
thanks for your reply.
Here is my problem. While deploying my reports on the other server we don't want to create the datasource. Already in the deployment server datasources are created.
what we have to do his while deploying the reports we have to map the existing datasources to the deploying rdl files. Can you explain me how we can achieve this using reports scripts.
|||So, you have a pre-existing datasource on the server, and you want your newly-published report to use IT instead of the datasource the report was created with, right?
If so, try SetItemDataSources():
http://msdn2.microsoft.com/ru-ru/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.setitemdatasources.aspx
|||Russell,
Thanks for your reply.
I tried with that setitemdatasources, but it not setting the new datasource name for the rdl.
I don't know were i am making the mistake. please look into the code and correct me if the code is wrong.
Dim dataSources() As DataSource
dataSources = rs.GetItemDataSources("/Reports/Test Matrix")
Dim NewdataSources As new DataSource
Dim dsref As New DataSourceReference
dsref.Reference = "/Data Sources/TestDatasource"
NewdataSources.Item = CType(dsref,DataSourceDefinitionOrReference)
NewdataSources.Name = "NewDatasource"
dataSources(0) = NewdataSources
rs.SetItemDataSources("/Reports/Test Matrix", dataSources)
Simran Raj
sqlTuesday, March 20, 2012
Changing table owner SQL database
i'm looking for a script to change the ownership of some tables in SQL
database from a certain user to dbo
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi.
You can utilize something as this:
sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'
This it is a stored procedure of SQL Server and only is able used by the use
rs in the roles SysAdmin and db_owner.
Hermilson Tinoco.
****************************************
*************
i'm looking for a script to change the ownership of some tables in SQL
database from a certain user to dbo|||Thanks for your reply.
Via other sources I've got a mail with the following script which
creates a stored procedure that you can execute to change the ownership
of several tables in one run:
CREATE PROC dbo.up_FixObjOwners
AS
SET NOCOUNT ON
DECLARE @.dynsql varchar(1000)
SET @.dynsql = ''
DECLARE @.Obj_Owner sysname
SET @.Obj_Owner = ''
DECLARE @.Obj_Type VARCHAR(30)
SET @.Obj_Type = ''
DECLARE @.Obj_Name sysname
SET @.Obj_Name = ''
DECLARE @.ObjCounter INT
SET @.ObjCounter = 0
DECLARE @.DBO CHAR(3)
SET @.DBO = 'DBO'
-- temp table to hold all objects not owned
-- by DBO
create table #ChangeOwners(
id int identity(1,1),
Obj_Owner sysname,
Obj_Name sysname,
Obj_Type varchar(30))
-- populate it
INSERT #ChangeOwners (Obj_Owner, Obj_Name, Obj_Type)
select
su.name,
so.name,
case
when type = 'u' then 'table'
when type = 'p' then 'sproc'
when type = 'v' then 'view'
end as obj_type
from sysusers su
join sysobjects so
on su.uid = so.uid
where su.name not in ('information_schema', 'dbo')
and so.type in ('p', 'u', 'v')
-- select * from #ChangeOwners
SET @.ObjCounter = @.@.rowcount -- holds the count of rows inserted into
#ChangeOwners
WHILE @.Objcounter > 0
BEGIN
-- construct string for object ownership change
SELECT @.Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE
id = @.ObjCounter
SELECT @.Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @.ObjCounter
SET @.dynsql = 'sp_ChangeObjectOwner ''' + @.Obj_Name + ''', ' + @.DBO
--select @.dynsql
print 'changing ownership on ' + @.Obj_Type + ': ' + @.Obj_Name
EXEC(@.dynsql)
SET @.ObjCounter = @.ObjCounter - 1
END
-- ok all done, collect garbage
drop table #ChangeOwners
I hope u can use some other time too.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Note that changing the object owner (or renaming the proc) will not change
the underlying source text of the procedure. This can cause problems with
subsequent DDL scripting if the original owner was explicitly specified on
the CREATE statement.
Hope this helps.
Dan Guzman
SQL Server MVP
"stevesilent" <stevesilent@.devdex.com> wrote in message
news:OV1wf8J8DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> Via other sources I've got a mail with the following script which
> creates a stored procedure that you can execute to change the ownership
> of several tables in one run:
> CREATE PROC dbo.up_FixObjOwners
> AS
> SET NOCOUNT ON
> DECLARE @.dynsql varchar(1000)
> SET @.dynsql = ''
> DECLARE @.Obj_Owner sysname
> SET @.Obj_Owner = ''
> DECLARE @.Obj_Type VARCHAR(30)
> SET @.Obj_Type = ''
> DECLARE @.Obj_Name sysname
> SET @.Obj_Name = ''
> DECLARE @.ObjCounter INT
> SET @.ObjCounter = 0
> DECLARE @.DBO CHAR(3)
> SET @.DBO = 'DBO'
> -- temp table to hold all objects not owned
> -- by DBO
> create table #ChangeOwners(
> id int identity(1,1),
> Obj_Owner sysname,
> Obj_Name sysname,
> Obj_Type varchar(30))
> -- populate it
> INSERT #ChangeOwners (Obj_Owner, Obj_Name, Obj_Type)
> select
> su.name,
> so.name,
> case
> when type = 'u' then 'table'
> when type = 'p' then 'sproc'
> when type = 'v' then 'view'
> end as obj_type
> from sysusers su
> join sysobjects so
> on su.uid = so.uid
> where su.name not in ('information_schema', 'dbo')
> and so.type in ('p', 'u', 'v')
> -- select * from #ChangeOwners
> SET @.ObjCounter = @.@.rowcount -- holds the count of rows inserted into
> #ChangeOwners
> WHILE @.Objcounter > 0
> BEGIN
> -- construct string for object ownership change
> SELECT @.Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE
> id = @.ObjCounter
> SELECT @.Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @.ObjCounter
> SET @.dynsql = 'sp_ChangeObjectOwner ''' + @.Obj_Name + ''', ' + @.DBO
> --select @.dynsql
> print 'changing ownership on ' + @.Obj_Type + ': ' + @.Obj_Name
> EXEC(@.dynsql)
> SET @.ObjCounter = @.ObjCounter - 1
> END
> -- ok all done, collect garbage
> drop table #ChangeOwners
>
> I hope u can use some other time too.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
database from a certain user to dbo
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi.
You can utilize something as this:
sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'
This it is a stored procedure of SQL Server and only is able used by the use
rs in the roles SysAdmin and db_owner.
Hermilson Tinoco.
****************************************
*************
i'm looking for a script to change the ownership of some tables in SQL
database from a certain user to dbo|||Thanks for your reply.
Via other sources I've got a mail with the following script which
creates a stored procedure that you can execute to change the ownership
of several tables in one run:
CREATE PROC dbo.up_FixObjOwners
AS
SET NOCOUNT ON
DECLARE @.dynsql varchar(1000)
SET @.dynsql = ''
DECLARE @.Obj_Owner sysname
SET @.Obj_Owner = ''
DECLARE @.Obj_Type VARCHAR(30)
SET @.Obj_Type = ''
DECLARE @.Obj_Name sysname
SET @.Obj_Name = ''
DECLARE @.ObjCounter INT
SET @.ObjCounter = 0
DECLARE @.DBO CHAR(3)
SET @.DBO = 'DBO'
-- temp table to hold all objects not owned
-- by DBO
create table #ChangeOwners(
id int identity(1,1),
Obj_Owner sysname,
Obj_Name sysname,
Obj_Type varchar(30))
-- populate it
INSERT #ChangeOwners (Obj_Owner, Obj_Name, Obj_Type)
select
su.name,
so.name,
case
when type = 'u' then 'table'
when type = 'p' then 'sproc'
when type = 'v' then 'view'
end as obj_type
from sysusers su
join sysobjects so
on su.uid = so.uid
where su.name not in ('information_schema', 'dbo')
and so.type in ('p', 'u', 'v')
-- select * from #ChangeOwners
SET @.ObjCounter = @.@.rowcount -- holds the count of rows inserted into
#ChangeOwners
WHILE @.Objcounter > 0
BEGIN
-- construct string for object ownership change
SELECT @.Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE
id = @.ObjCounter
SELECT @.Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @.ObjCounter
SET @.dynsql = 'sp_ChangeObjectOwner ''' + @.Obj_Name + ''', ' + @.DBO
--select @.dynsql
print 'changing ownership on ' + @.Obj_Type + ': ' + @.Obj_Name
EXEC(@.dynsql)
SET @.ObjCounter = @.ObjCounter - 1
END
-- ok all done, collect garbage
drop table #ChangeOwners
I hope u can use some other time too.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Note that changing the object owner (or renaming the proc) will not change
the underlying source text of the procedure. This can cause problems with
subsequent DDL scripting if the original owner was explicitly specified on
the CREATE statement.
Hope this helps.
Dan Guzman
SQL Server MVP
"stevesilent" <stevesilent@.devdex.com> wrote in message
news:OV1wf8J8DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> Via other sources I've got a mail with the following script which
> creates a stored procedure that you can execute to change the ownership
> of several tables in one run:
> CREATE PROC dbo.up_FixObjOwners
> AS
> SET NOCOUNT ON
> DECLARE @.dynsql varchar(1000)
> SET @.dynsql = ''
> DECLARE @.Obj_Owner sysname
> SET @.Obj_Owner = ''
> DECLARE @.Obj_Type VARCHAR(30)
> SET @.Obj_Type = ''
> DECLARE @.Obj_Name sysname
> SET @.Obj_Name = ''
> DECLARE @.ObjCounter INT
> SET @.ObjCounter = 0
> DECLARE @.DBO CHAR(3)
> SET @.DBO = 'DBO'
> -- temp table to hold all objects not owned
> -- by DBO
> create table #ChangeOwners(
> id int identity(1,1),
> Obj_Owner sysname,
> Obj_Name sysname,
> Obj_Type varchar(30))
> -- populate it
> INSERT #ChangeOwners (Obj_Owner, Obj_Name, Obj_Type)
> select
> su.name,
> so.name,
> case
> when type = 'u' then 'table'
> when type = 'p' then 'sproc'
> when type = 'v' then 'view'
> end as obj_type
> from sysusers su
> join sysobjects so
> on su.uid = so.uid
> where su.name not in ('information_schema', 'dbo')
> and so.type in ('p', 'u', 'v')
> -- select * from #ChangeOwners
> SET @.ObjCounter = @.@.rowcount -- holds the count of rows inserted into
> #ChangeOwners
> WHILE @.Objcounter > 0
> BEGIN
> -- construct string for object ownership change
> SELECT @.Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE
> id = @.ObjCounter
> SELECT @.Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @.ObjCounter
> SET @.dynsql = 'sp_ChangeObjectOwner ''' + @.Obj_Name + ''', ' + @.DBO
> --select @.dynsql
> print 'changing ownership on ' + @.Obj_Type + ': ' + @.Obj_Name
> EXEC(@.dynsql)
> SET @.ObjCounter = @.ObjCounter - 1
> END
> -- ok all done, collect garbage
> drop table #ChangeOwners
>
> I hope u can use some other time too.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
Changing SQL-command with a script
Hello
I want to change the sql-command that is behind a ole db datasource with a script
So that first the script is run and that the sql-command is changed
Somebody who has any idea?
You cannot access object or change their properties from Script in SSIS, a somewhat dramatic change from DTS.
First choice would be to use an Property Expression on the SqlCommand property, but unfortunately this property has not been exposed as an expression.
Next choice would be to use the "SQL Command from variable". You can use a script to set the variable, and the source can be set to use that variable, so job done. I woudl actually be tempted to set the SQL through an expression on the variable. Highlight teh variable in the Variables pane and select the properties grid. You can then set EvaluateAsExpression, followed by the Expression itself.|||Thats indeed a dramatic change
Can you do it then in Microsoft Visual Studio for Application
Thx|||
g4rc wrote:
Thats indeed a dramatic change Can you do it then in Microsoft Visual Studio for Application
Thx
In the context of SSIS, a script task and VSA are the same thing. So the answer is "No".
You should use the approach outlined by Darren in the third paragraph of his earlier post. Its very easy - far easier than ActiveX scripting.
-Jamie|||can you give me a link to the post
I cannot find it|||
can you give me a link to the post
I cannot find it
Its at the top of this page
i.e. The first reply on this discussion thread.
-Jamie
Sunday, March 11, 2012
Changing Servers Collation
I am considering two solutions for changing the collation on a Server, the first is to follow the advise of BOL and script all the user DB's, export the data to text files or another media and having deleted these databases from the server use the Rebuildm.exe utility to reinstall the Master database with the new collation. Then run the scripts to recreate the DB's and import the data back i
The second is to script the dropping and re-creation of all indexes and keys and then change the column collations using the following script to generate the alter table statements required and then change the database collation. Once this is done I can detach the user databases and use the Rebuildm.exe utility, then I simply re-attach the DB's
I have 75 user databases on the Server in question, any suggestions on which method would be better
many thank
E
select x
case so.xtype
when 'u' then 'alter table ' + so.name + ' alter column ' + sc.name + ' ' + st.name + '(
+ cast(sc.length as varchar(5)) + ') COLLATE Latin1_General_CI_AS;
EN
from syscolumns sc, sysobjects so, systypes s
where so.id = sc.i
and sc.xtype = st.xusertyp
and so.xtype = 'u
and so.name <> 'dtproperties
and st.xusertype in (175, 239, 99, 231, 35, 167
and sc.collation = 'SQL_Latin1_General_CP1_CI_AS'Personally I would go for the first, as its the bol
recommendation.
Out of curiousity have you thought of going down the
unicode route ?
J
>--Original Message--
>I am considering two solutions for changing the collation
on a Server, the first is to follow the advise of BOL and
script all the user DB's, export the data to text files or
another media and having deleted these databases from the
server use the Rebuildm.exe utility to reinstall the
Master database with the new collation. Then run the
scripts to recreate the DB's and import the data back in
>The second is to script the dropping and re-creation of
all indexes and keys and then change the column collations
using the following script to generate the alter table
statements required and then change the database
collation. Once this is done I can detach the user
databases and use the Rebuildm.exe utility, then I simply
re-attach the DB's.
>I have 75 user databases on the Server in question, any
suggestions on which method would be better.
>many thanks
>Ed
>select x => case so.xtype
> when 'u' then 'alter table ' + so.name
+ ' alter column ' + sc.name + ' ' + st.name + '('
> + cast(sc.length as varchar(5))
+ ') COLLATE Latin1_General_CI_AS;'
> END
>from syscolumns sc, sysobjects so, systypes st
>where so.id = sc.id
>and sc.xtype = st.xusertype
>and so.xtype = 'u'
>and so.name <> 'dtproperties'
>and st.xusertype in (175, 239, 99, 231, 35, 167)
>and sc.collation = 'SQL_Latin1_General_CP1_CI_AS'
>.
>|||Unfortunatly I have inherited a large number of databases with tables that have a row width coming close to or excreding the 8000 charcter limit and yes I have tried repeatedly to get this sorted out by normalising the tables but its not to be
The second is to script the dropping and re-creation of all indexes and keys and then change the column collations using the following script to generate the alter table statements required and then change the database collation. Once this is done I can detach the user databases and use the Rebuildm.exe utility, then I simply re-attach the DB's
I have 75 user databases on the Server in question, any suggestions on which method would be better
many thank
E
select x
case so.xtype
when 'u' then 'alter table ' + so.name + ' alter column ' + sc.name + ' ' + st.name + '(
+ cast(sc.length as varchar(5)) + ') COLLATE Latin1_General_CI_AS;
EN
from syscolumns sc, sysobjects so, systypes s
where so.id = sc.i
and sc.xtype = st.xusertyp
and so.xtype = 'u
and so.name <> 'dtproperties
and st.xusertype in (175, 239, 99, 231, 35, 167
and sc.collation = 'SQL_Latin1_General_CP1_CI_AS'Personally I would go for the first, as its the bol
recommendation.
Out of curiousity have you thought of going down the
unicode route ?
J
>--Original Message--
>I am considering two solutions for changing the collation
on a Server, the first is to follow the advise of BOL and
script all the user DB's, export the data to text files or
another media and having deleted these databases from the
server use the Rebuildm.exe utility to reinstall the
Master database with the new collation. Then run the
scripts to recreate the DB's and import the data back in
>The second is to script the dropping and re-creation of
all indexes and keys and then change the column collations
using the following script to generate the alter table
statements required and then change the database
collation. Once this is done I can detach the user
databases and use the Rebuildm.exe utility, then I simply
re-attach the DB's.
>I have 75 user databases on the Server in question, any
suggestions on which method would be better.
>many thanks
>Ed
>select x => case so.xtype
> when 'u' then 'alter table ' + so.name
+ ' alter column ' + sc.name + ' ' + st.name + '('
> + cast(sc.length as varchar(5))
+ ') COLLATE Latin1_General_CI_AS;'
> END
>from syscolumns sc, sysobjects so, systypes st
>where so.id = sc.id
>and sc.xtype = st.xusertype
>and so.xtype = 'u'
>and so.name <> 'dtproperties'
>and st.xusertype in (175, 239, 99, 231, 35, 167)
>and sc.collation = 'SQL_Latin1_General_CP1_CI_AS'
>.
>|||Unfortunatly I have inherited a large number of databases with tables that have a row width coming close to or excreding the 8000 charcter limit and yes I have tried repeatedly to get this sorted out by normalising the tables but its not to be
Changing schedules for a Job outside SQL Server
Below is the script of a Job called "eFIMS_SendEmail" that I wish to
run. The intention is that every day of the week the job will execute
a SPROC at timed intervals. For example, the SundayRun schedule will
run once every 1 hours from 00:30:00 to 23:59:59
run. The intention is that every day of the week the job will execute
a SPROC at timed intervals. For example, the SundayRun schedule will
run once every 1 hours from 00:30:00 to 23:59:59
However, the clients have stated that they want an interface to this to
enable them easily to change the start time and frequency interval for
each day. It's an easy matter for me to paint them a form from within
the target application to enable the user to enter the start time and
interval for each day. I can then pass these as parameters to a SPROC.
How can I use these values to change the schedules for the job? For
example, if wanted to change SundayRun from once every 1 hours to once
every 30 mins? I know I could do it the hard way, by using string
manipulation (e.g. find string 'SundayRun', then look for the next
occurrence of @.active_start_time, @.freq_subday_type,
@.freq_subday_interval etc. and do some replacement) but this seems
somewhat tricky.
Many thanks
Edward
-- Script generated on 8/31/2006 9:27 AM
-- By: sa
-- Server: BISMARK
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name =
N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'eFIMS_SendEmail')
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''eFIMS_SendEmail'' since there
is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @.job_name = N'eFIMS_SendEmail'
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'eFIMS_SendEmail', @.owner_login_name = N'sa', @.description
= N'No description available.', @.category_name = N'[Uncategorized
(Local)]', @.enabled = 0, @.notify_level_email = 0, @.notify_level_page =
0, @.notify_level_netsend = 0, @.notify_level_eventlog = 2,
@.delete_level= 0
IF (@.@.ERROR <0 OR @.ReturnCode <0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID,
@.step_id = 1, @.step_name = N'SendEmail', @.command = N'EXEC
stpSendEmailConfirmation', @.database_name = N'194-eFIMS', @.server =
N'', @.database_user_name = N'', @.subsystem = N'TSQL',
@.cmdexec_success_code = 0, @.flags = 4, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <0 OR @.ReturnCode <0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <0 OR @.ReturnCode <0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID,
@.name = N'SundayRun', @.enabled = 1, @.freq_type = 8, @.active_start_date
= 20060831, @.active_start_time = 3000, @.freq_interval = 1,
@.freq_subday_type = 8, @.freq_subday_interval = 1,
@.freq_relative_interval = 0, @.freq_recurrence_factor = 1,
@.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <0 OR @.ReturnCode <0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID,
@.name = N'MondayRun', @.enabled = 1, @.freq_type = 8, @.active_start_date
= 20060831, @.active_start_time = 3000, @.freq_interval = 2,
@.freq_subday_type = 4, @.freq_subday_interval = 30,
@.freq_relative_interval = 0, @.freq_recurrence_factor = 1,
@.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <0 OR @.ReturnCode <0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID,
@.name = N'TuesdayRun', @.enabled = 1, @.freq_type = 8, @.active_start_date
= 20060831, @.active_start_time = 4000, @.freq_interval = 4,
@.freq_subday_type = 4, @.freq_subday_interval = 40,
@.freq_relative_interval = 0, @.freq_recurrence_factor = 1,
@.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <0 OR @.ReturnCode <0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID,
@.name = N'WednesdayRun', @.enabled = 1, @.freq_type = 8,
@.active_start_date = 20060830, @.active_start_time = 3000,
@.freq_interval = 8, @.freq_subday_type = 4, @.freq_subday_interval = 30,
@.freq_relative_interval = 0, @.freq_recurrence_factor = 1,
@.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <0 OR @.ReturnCode <0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID,
@.name = N'ThursdayRun', @.enabled = 1, @.freq_type = 8,
@.active_start_date = 20060831, @.active_start_time = 3500,
@.freq_interval = 16, @.freq_subday_type = 4, @.freq_subday_interval = 35,
@.freq_relative_interval = 0, @.freq_recurrence_factor = 1,
@.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <0 OR @.ReturnCode <0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID,
@.name = N'FridayRun', @.enabled = 1, @.freq_type = 8, @.active_start_date
= 20060831, @.active_start_time = 3000, @.freq_interval = 32,
@.freq_subday_type = 4, @.freq_subday_interval = 30,
@.freq_relative_interval = 0, @.freq_recurrence_factor = 1,
@.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <0 OR @.ReturnCode <0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID,
@.name = N'SaturdayRun', @.enabled = 1, @.freq_type = 8,
@.active_start_date = 20060831, @.active_start_time = 0, @.freq_interval =
64, @.freq_subday_type = 8, @.freq_subday_interval = 1,
@.freq_relative_interval = 0, @.freq_recurrence_factor = 1,
@.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <0 OR @.ReturnCode <0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <0 OR @.ReturnCode <0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT 0) ROLLBACK TRANSACTION
EndSave:teddysnips@.hotmail.com wrote:
Repeat after me - "Look in BOL before posting; Look in BOL before
posting"
- sorry to waste all your time!
Edward
Thursday, March 8, 2012
Changing SA password to "blank" (null)
We are about to start a migration and one of the requirements from the script
is to have both sa passwords set to blank (no password) while the migration
takes place. Where do I go to change the SA password on a sql 2000 server and
a sql 7.0 server?
My expertise isn't in SQL so any steps would be very helpful!
Thanks!!!"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
> script
> is to have both sa passwords set to blank (no password) while the
> migration
> takes place. Where do I go to change the SA password on a sql 2000 server
> and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!
SQL Enterprise Manager -> Security
Pick the sa login and change the password.
Rick Sawtell
MCT, MCSD, MCDBA|||Query Analyzer works.
exec sp_password
Read about it within Books Online (within the SQL Server program group).
--
Keith
"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
script
> is to have both sa passwords set to blank (no password) while the
migration
> takes place. Where do I go to change the SA password on a sql 2000 server
and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!|||"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
script
> is to have both sa passwords set to blank (no password) while the
migration
> takes place.
Huh? I'd really question WHY?
In any case if you do this, make darn sure you have the machines isolated
from the Internet via a firewall or something. A number of exploits exist
that look for and exploit blank passwords.
> Where do I go to change the SA password on a sql 2000 server and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!
is to have both sa passwords set to blank (no password) while the migration
takes place. Where do I go to change the SA password on a sql 2000 server and
a sql 7.0 server?
My expertise isn't in SQL so any steps would be very helpful!
Thanks!!!"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
> script
> is to have both sa passwords set to blank (no password) while the
> migration
> takes place. Where do I go to change the SA password on a sql 2000 server
> and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!
SQL Enterprise Manager -> Security
Pick the sa login and change the password.
Rick Sawtell
MCT, MCSD, MCDBA|||Query Analyzer works.
exec sp_password
Read about it within Books Online (within the SQL Server program group).
--
Keith
"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
script
> is to have both sa passwords set to blank (no password) while the
migration
> takes place. Where do I go to change the SA password on a sql 2000 server
and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!|||"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
script
> is to have both sa passwords set to blank (no password) while the
migration
> takes place.
Huh? I'd really question WHY?
In any case if you do this, make darn sure you have the machines isolated
from the Internet via a firewall or something. A number of exploits exist
that look for and exploit blank passwords.
> Where do I go to change the SA password on a sql 2000 server and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!
Sunday, February 19, 2012
Changing Identity Seed
Hi All,
I am using SQL Server 2000 and I am trying to change the identity seed in a
table. I want to change this via a script. I have used the following command:
DBCC CHECKIDENT (dbo, RESEED, value)
where
dbo = the table in the database whose seed I want to change
value = the value I would like to change the value in Identity seed too.
I get the following result:
"Checking identity information: current identity value '100017140', current
column value '100017148'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Then when I go to the table --> right click --> desigh --> the identity seed
has not changed to what the message above has said it has changed too.
I have tried refreshing the table but still no luck.
Can anyone help. I am looking to have the value to be changed in the design
view for Identity seed.
Thanks.
A
Thanks
AaaaaAaaa
It works just fine (why do you call the table 'dbo'?)
create table test (c int not null identity(1,1))
go
insert into test default values
insert into test default values
insert into test default values
go
select * from test --we have 3 rows
/*
c
--
1
2
3
*/
go
dbcc checkident (test, RESEED, 1)
insert into test default values
insert into test default values
insert into test default values
select * from test
drop table test
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
> Hi All,
> I am using SQL Server 2000 and I am trying to change the identity seed in
> a
> table. I want to change this via a script. I have used the following
> command:
> DBCC CHECKIDENT (dbo, RESEED, value)
> where
> dbo = the table in the database whose seed I want to change
> value = the value I would like to change the value in Identity seed too.
> I get the following result:
> "Checking identity information: current identity value '100017140',
> current
> column value '100017148'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
> Can anyone help. I am looking to have the value to be changed in the
> design
> view for Identity seed.
> Thanks.
> A
> Thanks
> Aaaaa|||Hi Uri
Thanks for responding. I only wrote dbo instead of test.
I created a test table like you suggested. Then when you right click on the
test table --> go to design --> at the bottom is a table where the following
is written:
Columns
Description
...
..
Identity Yes
Identity Seed 1 --> this is what i
would like to
Idenitity Increment 1 change, but
the code does not
.... do
so.
....
I am new to SQL Server 2000 and this forum, I am not sure if I am typing or
checking something wrong.
Please bear with the little knowledge I have.
Thank you so much.
A
Thanks
Aaaaa
"Uri Dimant" wrote:
> Aaaa
> It works just fine (why do you call the table 'dbo'?)
> create table test (c int not null identity(1,1))
> go
> insert into test default values
> insert into test default values
> insert into test default values
> go
> select * from test --we have 3 rows
> /*
> c
> --
> 1
> 2
> 3
> */
> go
> dbcc checkident (test, RESEED, 1)
> insert into test default values
> insert into test default values
> insert into test default values
> select * from test
> drop table test
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
> > Hi All,
> >
> > I am using SQL Server 2000 and I am trying to change the identity seed in
> > a
> > table. I want to change this via a script. I have used the following
> > command:
> >
> > DBCC CHECKIDENT (dbo, RESEED, value)
> > where
> > dbo = the table in the database whose seed I want to change
> > value = the value I would like to change the value in Identity seed too.
> >
> > I get the following result:
> > "Checking identity information: current identity value '100017140',
> > current
> > column value '100017148'.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator."
> >
> > Then when I go to the table --> right click --> desigh --> the identity
> > seed
> > has not changed to what the message above has said it has changed too.
> >
> > I have tried refreshing the table but still no luck.
> >
> > Can anyone help. I am looking to have the value to be changed in the
> > design
> > view for Identity seed.
> >
> > Thanks.
> > A
> >
> > Thanks
> > Aaaaa
>
>|||> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
Did you try inserting a row into the table? Did you try closing and
re-opening Management Studio?
--
Aaron Bertrand
SQL Server MVP|||> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
I inserted a new table, shut down the application and opened it up. When it
opened the table had been updated (test --> right click --> open table -->
return all rows)
But then when I check design --> column --> identity seed the value has not
changed.
This is where the problem lies.
A
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Then when I go to the table --> right click --> desigh --> the identity
> > seed
> > has not changed to what the message above has said it has changed too.
> >
> > I have tried refreshing the table but still no luck.
> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
<Aaaaa@.discussions.microsoft.com> wrote:
>I inserted a new table, shut down the application and opened it up. When it
>opened the table had been updated (test --> right click --> open table -->
>return all rows)
>But then when I check design --> column --> identity seed the value has not
>changed.
>This is where the problem lies.
Be sure to right-click on the table in the tree and choose REFRESH.
Roy Harvey
Beacon Falls, CT|||Hi
I definately have refreshed each time. Still no joy. It seems to update
the table but not the design view.
A
--
Thanks
Aaaaa
"Roy Harvey" wrote:
> On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
> <Aaaaa@.discussions.microsoft.com> wrote:
> >I inserted a new table, shut down the application and opened it up. When it
> >opened the table had been updated (test --> right click --> open table -->
> >return all rows)
> >But then when I check design --> column --> identity seed the value has not
> >changed.
> >
> >This is where the problem lies.
> Be sure to right-click on the table in the tree and choose REFRESH.
> Roy Harvey
> Beacon Falls, CT
>|||> Be sure to right-click on the table in the tree and choose REFRESH.
No, this really is a bug in the table designer. I can reproduce it in 2000,
2005 and even in Katmai. There doesn't seem to be a way to make the table
designer reflect numerous changes to the identity seed value...
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
(The question, of course, is where on earth does SQL Server store the 1,1
from initial creation? They must be coming from somewhere. Yes, I'm too
lazy this morning to fire up profiler.)
--
Aaron Bertrand
SQL Server MVP|||Thanks Aaron
I have tried to run a trace via SQL Profiler though I am having some issues
with the parameters returning more indepth information on where (1,1) is
stored. Could you advise on what I could include with this for more
information?
Morning by the way (evening for me)
Thanks
Ads
Aaaaa
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Be sure to right-click on the table in the tree and choose REFRESH.
> No, this really is a bug in the table designer. I can reproduce it in 2000,
> 2005 and even in Katmai. There doesn't seem to be a way to make the table
> designer reflect numerous changes to the identity seed value...
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
> (The question, of course, is where on earth does SQL Server store the 1,1
> from initial creation? They must be coming from somewhere. Yes, I'm too
> lazy this morning to fire up profiler.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||In SQL 2008, I picked this up from TSQL:StmtCompleted:
select col.name, col.column_id,
st.name as DT_name,
schema_name(st.schema_id) as DT_schema,
col.max_length, col.precision, col.scale, bt.name as BT_name,
col.collation_name, col.is_nullable, col.is_ansi_padded,
col.is_rowguidcol, col.is_identity,
case when(idc.column_id is null)
then null else CONVERT(nvarchar(40), idc.seed_value) end,
case when(idc.column_id is null) then null
else CONVERT(nvarchar(40), idc.increment_value) end,
CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
as is_computed,
convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
as IsIdNotForRepl,
col.is_replicated,
col.is_non_sql_subscribed, col.is_merge_published,
col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
is_FullTextCol,
col_name(col.object_id, ftc.type_column_id) FT_type_column,
ftc.language_id as FT_language_id,
case when(cmc.column_id is null) then null else cmc.definition end as
formular,
case when(cmc.column_id is null) then null else cmc.is_persisted end as
is_persisted,
defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
'IsDeterministic')
as IsDeterministic, xmlcoll.name as xmlSchema_name,
schema_name(xmlcoll.schema_id)
as xmlSchema_schema, col.is_xml_document from sys.columns col
left outer join sys.types st on st.user_type_id = col.user_type_id left
outer join
sys.types bt on bt.user_type_id = col.system_type_id
left outer join sys.objects robj on robj.object_id = col.rule_object_id
and robj.type = 'R' left outer join sys.objects dobj on
dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
sys.default_constraints defCst on defCst.parent_object_id = col.object_id
and defCst.parent_column_id = col.column_id left outer join
sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id
= col.column_id left outer join sys.computed_columns cmc on cmc.object_id =col.object_id and cmc.column_id = col.column_id left outer join
sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
ftc.column_id = col.column_id left outer join sys.xml_schema_collections
xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
col.object_id = object_id(N'dbo.foo') order by col.column_id
Which I whittled down to:
SELECT name,seed_value,increment_value,last_value
FROM sys.identity_columns
WHERE [object_id] = OBJECT_ID('dbo.foo');
The result was:
bar, 1, 1, 5
Notice that seed_value in sys.identity_columns has not changed, though
current_value has.
--
Aaron Bertrand
SQL Server MVP
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...
> Thanks Aaron
> I have tried to run a trace via SQL Profiler though I am having some
> issues
> with the parameters returning more indepth information on where (1,1) is
> stored. Could you advise on what I could include with this for more
> information?
> Morning by the way (evening for me)
> Thanks
> Ads
> Aaaaa
> --
> Thanks
> Aaaaa
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> > Be sure to right-click on the table in the tree and choose REFRESH.
>> No, this really is a bug in the table designer. I can reproduce it in
>> 2000,
>> 2005 and even in Katmai. There doesn't seem to be a way to make the
>> table
>> designer reflect numerous changes to the identity seed value...
>> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
>> (The question, of course, is where on earth does SQL Server store the 1,1
>> from initial creation? They must be coming from somewhere. Yes, I'm too
>> lazy this morning to fire up profiler.)
>> --
>> Aaron Bertrand
>> SQL Server MVP
>>
>>|||That was very well shrunk BUT i still failed miserably and this code did not
work. Too many errors,
> SELECT name,seed_value,increment_value,last_value
> FROM sys.identity_columns
> WHERE [object_id] = OBJECT_ID('dbo.foo');
The system did not like 'sys.identity_columns' for a start.
Sorry about bugging you on this. This bug is really bugging me.
Would the first line be formatted like this?
SELECT test,24,1,21
Ads
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> In SQL 2008, I picked this up from TSQL:StmtCompleted:
> select col.name, col.column_id,
> st.name as DT_name,
> schema_name(st.schema_id) as DT_schema,
> col.max_length, col.precision, col.scale, bt.name as BT_name,
> col.collation_name, col.is_nullable, col.is_ansi_padded,
> col.is_rowguidcol, col.is_identity,
> case when(idc.column_id is null)
> then null else CONVERT(nvarchar(40), idc.seed_value) end,
> case when(idc.column_id is null) then null
> else CONVERT(nvarchar(40), idc.increment_value) end,
> CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
> as is_computed,
> convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
> as IsIdNotForRepl,
> col.is_replicated,
> col.is_non_sql_subscribed, col.is_merge_published,
> col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
> schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
> OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
> dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
> CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
> is_FullTextCol,
> col_name(col.object_id, ftc.type_column_id) FT_type_column,
> ftc.language_id as FT_language_id,
> case when(cmc.column_id is null) then null else cmc.definition end as
> formular,
> case when(cmc.column_id is null) then null else cmc.is_persisted end as
> is_persisted,
> defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
> 'IsDeterministic')
> as IsDeterministic, xmlcoll.name as xmlSchema_name,
> schema_name(xmlcoll.schema_id)
> as xmlSchema_schema, col.is_xml_document from sys.columns col
> left outer join sys.types st on st.user_type_id = col.user_type_id left
> outer join
> sys.types bt on bt.user_type_id = col.system_type_id
> left outer join sys.objects robj on robj.object_id = col.rule_object_id
> and robj.type = 'R' left outer join sys.objects dobj on
> dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
> sys.default_constraints defCst on defCst.parent_object_id = col.object_id
> and defCst.parent_column_id = col.column_id left outer join
> sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id
> = col.column_id left outer join sys.computed_columns cmc on cmc.object_id => col.object_id and cmc.column_id = col.column_id left outer join
> sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
> ftc.column_id = col.column_id left outer join sys.xml_schema_collections
> xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
> col.object_id = object_id(N'dbo.foo') order by col.column_id
> Which I whittled down to:
> SELECT name,seed_value,increment_value,last_value
> FROM sys.identity_columns
> WHERE [object_id] = OBJECT_ID('dbo.foo');
> The result was:
> bar, 1, 1, 5
> Notice that seed_value in sys.identity_columns has not changed, though
> current_value has.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...
> > Thanks Aaron
> >
> > I have tried to run a trace via SQL Profiler though I am having some
> > issues
> > with the parameters returning more indepth information on where (1,1) is
> > stored. Could you advise on what I could include with this for more
> > information?
> >
> > Morning by the way (evening for me)
> >
> > Thanks
> > Ads
> > Aaaaa
> > --
> > Thanks
> > Aaaaa
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> > Be sure to right-click on the table in the tree and choose REFRESH.
> >>
> >> No, this really is a bug in the table designer. I can reproduce it in
> >> 2000,
> >> 2005 and even in Katmai. There doesn't seem to be a way to make the
> >> table
> >> designer reflect numerous changes to the identity seed value...
> >>
> >> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
> >>
> >> (The question, of course, is where on earth does SQL Server store the 1,1
> >> from initial creation? They must be coming from somewhere. Yes, I'm too
> >> lazy this morning to fire up profiler.)
> >>
> >> --
> >> Aaron Bertrand
> >> SQL Server MVP
> >>
> >>
> >>
> >>
>
>|||> That was very well shrunk BUT i still failed miserably and this code did
> not
> work. Too many errors,
I know, as I stated, this was for SQL 2005/2008, not for 2000. You will
have to turn Profiler on, including TSQL:StmtCompleted, and then open the
table in design view. There will be a bunch of statements there, and one of
them will include something about identity / seed.
--
Aaron Bertrand
SQL Server MVP|||Hi
I tried turning on profiler and then checking the table in design view.
Unfortunately was not able to find any relating table to find where the
identity seed in design view is entered. Any other ideas?
Thank you so much for all your help.
Ads
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> > That was very well shrunk BUT i still failed miserably and this code did
> > not
> > work. Too many errors,
> I know, as I stated, this was for SQL 2005/2008, not for 2000. You will
> have to turn Profiler on, including TSQL:StmtCompleted, and then open the
> table in design view. There will be a bunch of statements there, and one of
> them will include something about identity / seed.
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||Does it really matter where it's stored? It's WRONG! I'd fire up profiler
and look at the same queries for you, but I don't think it's really worth
it. They're not likely to fix this for SQL Server 2000 anyway. But there
is a chance they will fix it for 2005, and an even better chance they will
fix it in 2008.
--
Aaron Bertrand
SQL Server MVP
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:83E7C60B-C39B-4B64-B7A6-60AA1DD09DB2@.microsoft.com...
> Hi
> I tried turning on profiler and then checking the table in design view.
> Unfortunately was not able to find any relating table to find where the
> identity seed in design view is entered. Any other ideas?
> Thank you so much for all your help.
I am using SQL Server 2000 and I am trying to change the identity seed in a
table. I want to change this via a script. I have used the following command:
DBCC CHECKIDENT (dbo, RESEED, value)
where
dbo = the table in the database whose seed I want to change
value = the value I would like to change the value in Identity seed too.
I get the following result:
"Checking identity information: current identity value '100017140', current
column value '100017148'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Then when I go to the table --> right click --> desigh --> the identity seed
has not changed to what the message above has said it has changed too.
I have tried refreshing the table but still no luck.
Can anyone help. I am looking to have the value to be changed in the design
view for Identity seed.
Thanks.
A
Thanks
AaaaaAaaa
It works just fine (why do you call the table 'dbo'?)
create table test (c int not null identity(1,1))
go
insert into test default values
insert into test default values
insert into test default values
go
select * from test --we have 3 rows
/*
c
--
1
2
3
*/
go
dbcc checkident (test, RESEED, 1)
insert into test default values
insert into test default values
insert into test default values
select * from test
drop table test
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
> Hi All,
> I am using SQL Server 2000 and I am trying to change the identity seed in
> a
> table. I want to change this via a script. I have used the following
> command:
> DBCC CHECKIDENT (dbo, RESEED, value)
> where
> dbo = the table in the database whose seed I want to change
> value = the value I would like to change the value in Identity seed too.
> I get the following result:
> "Checking identity information: current identity value '100017140',
> current
> column value '100017148'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
> Can anyone help. I am looking to have the value to be changed in the
> design
> view for Identity seed.
> Thanks.
> A
> Thanks
> Aaaaa|||Hi Uri
Thanks for responding. I only wrote dbo instead of test.
I created a test table like you suggested. Then when you right click on the
test table --> go to design --> at the bottom is a table where the following
is written:
Columns
Description
...
..
Identity Yes
Identity Seed 1 --> this is what i
would like to
Idenitity Increment 1 change, but
the code does not
.... do
so.
....
I am new to SQL Server 2000 and this forum, I am not sure if I am typing or
checking something wrong.
Please bear with the little knowledge I have.
Thank you so much.
A
Thanks
Aaaaa
"Uri Dimant" wrote:
> Aaaa
> It works just fine (why do you call the table 'dbo'?)
> create table test (c int not null identity(1,1))
> go
> insert into test default values
> insert into test default values
> insert into test default values
> go
> select * from test --we have 3 rows
> /*
> c
> --
> 1
> 2
> 3
> */
> go
> dbcc checkident (test, RESEED, 1)
> insert into test default values
> insert into test default values
> insert into test default values
> select * from test
> drop table test
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
> > Hi All,
> >
> > I am using SQL Server 2000 and I am trying to change the identity seed in
> > a
> > table. I want to change this via a script. I have used the following
> > command:
> >
> > DBCC CHECKIDENT (dbo, RESEED, value)
> > where
> > dbo = the table in the database whose seed I want to change
> > value = the value I would like to change the value in Identity seed too.
> >
> > I get the following result:
> > "Checking identity information: current identity value '100017140',
> > current
> > column value '100017148'.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator."
> >
> > Then when I go to the table --> right click --> desigh --> the identity
> > seed
> > has not changed to what the message above has said it has changed too.
> >
> > I have tried refreshing the table but still no luck.
> >
> > Can anyone help. I am looking to have the value to be changed in the
> > design
> > view for Identity seed.
> >
> > Thanks.
> > A
> >
> > Thanks
> > Aaaaa
>
>|||> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
Did you try inserting a row into the table? Did you try closing and
re-opening Management Studio?
--
Aaron Bertrand
SQL Server MVP|||> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
I inserted a new table, shut down the application and opened it up. When it
opened the table had been updated (test --> right click --> open table -->
return all rows)
But then when I check design --> column --> identity seed the value has not
changed.
This is where the problem lies.
A
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Then when I go to the table --> right click --> desigh --> the identity
> > seed
> > has not changed to what the message above has said it has changed too.
> >
> > I have tried refreshing the table but still no luck.
> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
<Aaaaa@.discussions.microsoft.com> wrote:
>I inserted a new table, shut down the application and opened it up. When it
>opened the table had been updated (test --> right click --> open table -->
>return all rows)
>But then when I check design --> column --> identity seed the value has not
>changed.
>This is where the problem lies.
Be sure to right-click on the table in the tree and choose REFRESH.
Roy Harvey
Beacon Falls, CT|||Hi
I definately have refreshed each time. Still no joy. It seems to update
the table but not the design view.
A
--
Thanks
Aaaaa
"Roy Harvey" wrote:
> On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
> <Aaaaa@.discussions.microsoft.com> wrote:
> >I inserted a new table, shut down the application and opened it up. When it
> >opened the table had been updated (test --> right click --> open table -->
> >return all rows)
> >But then when I check design --> column --> identity seed the value has not
> >changed.
> >
> >This is where the problem lies.
> Be sure to right-click on the table in the tree and choose REFRESH.
> Roy Harvey
> Beacon Falls, CT
>|||> Be sure to right-click on the table in the tree and choose REFRESH.
No, this really is a bug in the table designer. I can reproduce it in 2000,
2005 and even in Katmai. There doesn't seem to be a way to make the table
designer reflect numerous changes to the identity seed value...
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
(The question, of course, is where on earth does SQL Server store the 1,1
from initial creation? They must be coming from somewhere. Yes, I'm too
lazy this morning to fire up profiler.)
--
Aaron Bertrand
SQL Server MVP|||Thanks Aaron
I have tried to run a trace via SQL Profiler though I am having some issues
with the parameters returning more indepth information on where (1,1) is
stored. Could you advise on what I could include with this for more
information?
Morning by the way (evening for me)
Thanks
Ads
Aaaaa
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Be sure to right-click on the table in the tree and choose REFRESH.
> No, this really is a bug in the table designer. I can reproduce it in 2000,
> 2005 and even in Katmai. There doesn't seem to be a way to make the table
> designer reflect numerous changes to the identity seed value...
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
> (The question, of course, is where on earth does SQL Server store the 1,1
> from initial creation? They must be coming from somewhere. Yes, I'm too
> lazy this morning to fire up profiler.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||In SQL 2008, I picked this up from TSQL:StmtCompleted:
select col.name, col.column_id,
st.name as DT_name,
schema_name(st.schema_id) as DT_schema,
col.max_length, col.precision, col.scale, bt.name as BT_name,
col.collation_name, col.is_nullable, col.is_ansi_padded,
col.is_rowguidcol, col.is_identity,
case when(idc.column_id is null)
then null else CONVERT(nvarchar(40), idc.seed_value) end,
case when(idc.column_id is null) then null
else CONVERT(nvarchar(40), idc.increment_value) end,
CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
as is_computed,
convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
as IsIdNotForRepl,
col.is_replicated,
col.is_non_sql_subscribed, col.is_merge_published,
col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
is_FullTextCol,
col_name(col.object_id, ftc.type_column_id) FT_type_column,
ftc.language_id as FT_language_id,
case when(cmc.column_id is null) then null else cmc.definition end as
formular,
case when(cmc.column_id is null) then null else cmc.is_persisted end as
is_persisted,
defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
'IsDeterministic')
as IsDeterministic, xmlcoll.name as xmlSchema_name,
schema_name(xmlcoll.schema_id)
as xmlSchema_schema, col.is_xml_document from sys.columns col
left outer join sys.types st on st.user_type_id = col.user_type_id left
outer join
sys.types bt on bt.user_type_id = col.system_type_id
left outer join sys.objects robj on robj.object_id = col.rule_object_id
and robj.type = 'R' left outer join sys.objects dobj on
dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
sys.default_constraints defCst on defCst.parent_object_id = col.object_id
and defCst.parent_column_id = col.column_id left outer join
sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id
= col.column_id left outer join sys.computed_columns cmc on cmc.object_id =col.object_id and cmc.column_id = col.column_id left outer join
sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
ftc.column_id = col.column_id left outer join sys.xml_schema_collections
xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
col.object_id = object_id(N'dbo.foo') order by col.column_id
Which I whittled down to:
SELECT name,seed_value,increment_value,last_value
FROM sys.identity_columns
WHERE [object_id] = OBJECT_ID('dbo.foo');
The result was:
bar, 1, 1, 5
Notice that seed_value in sys.identity_columns has not changed, though
current_value has.
--
Aaron Bertrand
SQL Server MVP
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...
> Thanks Aaron
> I have tried to run a trace via SQL Profiler though I am having some
> issues
> with the parameters returning more indepth information on where (1,1) is
> stored. Could you advise on what I could include with this for more
> information?
> Morning by the way (evening for me)
> Thanks
> Ads
> Aaaaa
> --
> Thanks
> Aaaaa
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> > Be sure to right-click on the table in the tree and choose REFRESH.
>> No, this really is a bug in the table designer. I can reproduce it in
>> 2000,
>> 2005 and even in Katmai. There doesn't seem to be a way to make the
>> table
>> designer reflect numerous changes to the identity seed value...
>> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
>> (The question, of course, is where on earth does SQL Server store the 1,1
>> from initial creation? They must be coming from somewhere. Yes, I'm too
>> lazy this morning to fire up profiler.)
>> --
>> Aaron Bertrand
>> SQL Server MVP
>>
>>|||That was very well shrunk BUT i still failed miserably and this code did not
work. Too many errors,
> SELECT name,seed_value,increment_value,last_value
> FROM sys.identity_columns
> WHERE [object_id] = OBJECT_ID('dbo.foo');
The system did not like 'sys.identity_columns' for a start.
Sorry about bugging you on this. This bug is really bugging me.
Would the first line be formatted like this?
SELECT test,24,1,21
Ads
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> In SQL 2008, I picked this up from TSQL:StmtCompleted:
> select col.name, col.column_id,
> st.name as DT_name,
> schema_name(st.schema_id) as DT_schema,
> col.max_length, col.precision, col.scale, bt.name as BT_name,
> col.collation_name, col.is_nullable, col.is_ansi_padded,
> col.is_rowguidcol, col.is_identity,
> case when(idc.column_id is null)
> then null else CONVERT(nvarchar(40), idc.seed_value) end,
> case when(idc.column_id is null) then null
> else CONVERT(nvarchar(40), idc.increment_value) end,
> CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
> as is_computed,
> convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
> as IsIdNotForRepl,
> col.is_replicated,
> col.is_non_sql_subscribed, col.is_merge_published,
> col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
> schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
> OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
> dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
> CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
> is_FullTextCol,
> col_name(col.object_id, ftc.type_column_id) FT_type_column,
> ftc.language_id as FT_language_id,
> case when(cmc.column_id is null) then null else cmc.definition end as
> formular,
> case when(cmc.column_id is null) then null else cmc.is_persisted end as
> is_persisted,
> defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
> 'IsDeterministic')
> as IsDeterministic, xmlcoll.name as xmlSchema_name,
> schema_name(xmlcoll.schema_id)
> as xmlSchema_schema, col.is_xml_document from sys.columns col
> left outer join sys.types st on st.user_type_id = col.user_type_id left
> outer join
> sys.types bt on bt.user_type_id = col.system_type_id
> left outer join sys.objects robj on robj.object_id = col.rule_object_id
> and robj.type = 'R' left outer join sys.objects dobj on
> dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
> sys.default_constraints defCst on defCst.parent_object_id = col.object_id
> and defCst.parent_column_id = col.column_id left outer join
> sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id
> = col.column_id left outer join sys.computed_columns cmc on cmc.object_id => col.object_id and cmc.column_id = col.column_id left outer join
> sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
> ftc.column_id = col.column_id left outer join sys.xml_schema_collections
> xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
> col.object_id = object_id(N'dbo.foo') order by col.column_id
> Which I whittled down to:
> SELECT name,seed_value,increment_value,last_value
> FROM sys.identity_columns
> WHERE [object_id] = OBJECT_ID('dbo.foo');
> The result was:
> bar, 1, 1, 5
> Notice that seed_value in sys.identity_columns has not changed, though
> current_value has.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...
> > Thanks Aaron
> >
> > I have tried to run a trace via SQL Profiler though I am having some
> > issues
> > with the parameters returning more indepth information on where (1,1) is
> > stored. Could you advise on what I could include with this for more
> > information?
> >
> > Morning by the way (evening for me)
> >
> > Thanks
> > Ads
> > Aaaaa
> > --
> > Thanks
> > Aaaaa
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> > Be sure to right-click on the table in the tree and choose REFRESH.
> >>
> >> No, this really is a bug in the table designer. I can reproduce it in
> >> 2000,
> >> 2005 and even in Katmai. There doesn't seem to be a way to make the
> >> table
> >> designer reflect numerous changes to the identity seed value...
> >>
> >> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
> >>
> >> (The question, of course, is where on earth does SQL Server store the 1,1
> >> from initial creation? They must be coming from somewhere. Yes, I'm too
> >> lazy this morning to fire up profiler.)
> >>
> >> --
> >> Aaron Bertrand
> >> SQL Server MVP
> >>
> >>
> >>
> >>
>
>|||> That was very well shrunk BUT i still failed miserably and this code did
> not
> work. Too many errors,
I know, as I stated, this was for SQL 2005/2008, not for 2000. You will
have to turn Profiler on, including TSQL:StmtCompleted, and then open the
table in design view. There will be a bunch of statements there, and one of
them will include something about identity / seed.
--
Aaron Bertrand
SQL Server MVP|||Hi
I tried turning on profiler and then checking the table in design view.
Unfortunately was not able to find any relating table to find where the
identity seed in design view is entered. Any other ideas?
Thank you so much for all your help.
Ads
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> > That was very well shrunk BUT i still failed miserably and this code did
> > not
> > work. Too many errors,
> I know, as I stated, this was for SQL 2005/2008, not for 2000. You will
> have to turn Profiler on, including TSQL:StmtCompleted, and then open the
> table in design view. There will be a bunch of statements there, and one of
> them will include something about identity / seed.
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||Does it really matter where it's stored? It's WRONG! I'd fire up profiler
and look at the same queries for you, but I don't think it's really worth
it. They're not likely to fix this for SQL Server 2000 anyway. But there
is a chance they will fix it for 2005, and an even better chance they will
fix it in 2008.
--
Aaron Bertrand
SQL Server MVP
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:83E7C60B-C39B-4B64-B7A6-60AA1DD09DB2@.microsoft.com...
> Hi
> I tried turning on profiler and then checking the table in design view.
> Unfortunately was not able to find any relating table to find where the
> identity seed in design view is entered. Any other ideas?
> Thank you so much for all your help.
Changing Identity Seed
Hi All,
I am using SQL Server 2000 and I am trying to change the identity seed in a
table. I want to change this via a script. I have used the following command:
DBCC CHECKIDENT (dbo, RESEED, value)
where
dbo = the table in the database whose seed I want to change
value = the value I would like to change the value in Identity seed too.
I get the following result:
"Checking identity information: current identity value '100017140', current
column value '100017148'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Then when I go to the table --> right click --> desigh --> the identity seed
has not changed to what the message above has said it has changed too.
I have tried refreshing the table but still no luck.
Can anyone help. I am looking to have the value to be changed in the design
view for Identity seed.
Thanks.
A
Thanks
Aaaaa
Aaaa
It works just fine (why do you call the table 'dbo'?)
create table test (c int not null identity(1,1))
go
insert into test default values
insert into test default values
insert into test default values
go
select * from test --we have 3 rows
/*
c
1
2
3
*/
go
dbcc checkident (test, RESEED, 1)
insert into test default values
insert into test default values
insert into test default values
select * from test
drop table test
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
> Hi All,
> I am using SQL Server 2000 and I am trying to change the identity seed in
> a
> table. I want to change this via a script. I have used the following
> command:
> DBCC CHECKIDENT (dbo, RESEED, value)
> where
> dbo = the table in the database whose seed I want to change
> value = the value I would like to change the value in Identity seed too.
> I get the following result:
> "Checking identity information: current identity value '100017140',
> current
> column value '100017148'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
> Can anyone help. I am looking to have the value to be changed in the
> design
> view for Identity seed.
> Thanks.
> A
> Thanks
> Aaaaa
|||Hi Uri
Thanks for responding. I only wrote dbo instead of test.
I created a test table like you suggested. Then when you right click on the
test table --> go to design --> at the bottom is a table where the following
is written:
Columns
Description
...
...
Identity Yes
Identity Seed 1 --> this is what i
would like to
Idenitity Increment 1 change, but
the code does not
..... do
so.
.....
I am new to SQL Server 2000 and this forum, I am not sure if I am typing or
checking something wrong.
Please bear with the little knowledge I have.
Thank you so much.
A
Thanks
Aaaaa
"Uri Dimant" wrote:
> Aaaa
> It works just fine (why do you call the table 'dbo'?)
> create table test (c int not null identity(1,1))
> go
> insert into test default values
> insert into test default values
> insert into test default values
> go
> select * from test --we have 3 rows
> /*
> c
> --
> 1
> 2
> 3
> */
> go
> dbcc checkident (test, RESEED, 1)
> insert into test default values
> insert into test default values
> insert into test default values
> select * from test
> drop table test
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
>
>
|||> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
Did you try inserting a row into the table? Did you try closing and
re-opening Management Studio?
Aaron Bertrand
SQL Server MVP
|||> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
I inserted a new table, shut down the application and opened it up. When it
opened the table had been updated (test --> right click --> open table -->
return all rows)
But then when I check design --> column --> identity seed the value has not
changed.
This is where the problem lies.
A
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
> --
> Aaron Bertrand
> SQL Server MVP
>
>
|||On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
<Aaaaa@.discussions.microsoft.com> wrote:
>I inserted a new table, shut down the application and opened it up. When it
>opened the table had been updated (test --> right click --> open table -->
>return all rows)
>But then when I check design --> column --> identity seed the value has not
>changed.
>This is where the problem lies.
Be sure to right-click on the table in the tree and choose REFRESH.
Roy Harvey
Beacon Falls, CT
|||Hi
I definately have refreshed each time. Still no joy. It seems to update
the table but not the design view.
A
Thanks
Aaaaa
"Roy Harvey" wrote:
> On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
> <Aaaaa@.discussions.microsoft.com> wrote:
>
> Be sure to right-click on the table in the tree and choose REFRESH.
> Roy Harvey
> Beacon Falls, CT
>
|||> Be sure to right-click on the table in the tree and choose REFRESH.
No, this really is a bug in the table designer. I can reproduce it in 2000,
2005 and even in Katmai. There doesn't seem to be a way to make the table
designer reflect numerous changes to the identity seed value...
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
(The question, of course, is where on earth does SQL Server store the 1,1
from initial creation? They must be coming from somewhere. Yes, I'm too
lazy this morning to fire up profiler.)
Aaron Bertrand
SQL Server MVP
|||Thanks Aaron
I have tried to run a trace via SQL Profiler though I am having some issues
with the parameters returning more indepth information on where (1,1) is
stored. Could you advise on what I could include with this for more
information?
Morning by the way (evening for me)
Thanks
Ads
Aaaaa
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> No, this really is a bug in the table designer. I can reproduce it in 2000,
> 2005 and even in Katmai. There doesn't seem to be a way to make the table
> designer reflect numerous changes to the identity seed value...
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
> (The question, of course, is where on earth does SQL Server store the 1,1
> from initial creation? They must be coming from somewhere. Yes, I'm too
> lazy this morning to fire up profiler.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>
|||In SQL 2008, I picked this up from TSQL:StmtCompleted:
select col.name, col.column_id,
st.name as DT_name,
schema_name(st.schema_id) as DT_schema,
col.max_length, col.precision, col.scale, bt.name as BT_name,
col.collation_name, col.is_nullable, col.is_ansi_padded,
col.is_rowguidcol, col.is_identity,
case when(idc.column_id is null)
then null else CONVERT(nvarchar(40), idc.seed_value) end,
case when(idc.column_id is null) then null
else CONVERT(nvarchar(40), idc.increment_value) end,
CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
as is_computed,
convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
as IsIdNotForRepl,
col.is_replicated,
col.is_non_sql_subscribed, col.is_merge_published,
col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
is_FullTextCol,
col_name(col.object_id, ftc.type_column_id) FT_type_column,
ftc.language_id as FT_language_id,
case when(cmc.column_id is null) then null else cmc.definition end as
formular,
case when(cmc.column_id is null) then null else cmc.is_persisted end as
is_persisted,
defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
'IsDeterministic')
as IsDeterministic, xmlcoll.name as xmlSchema_name,
schema_name(xmlcoll.schema_id)
as xmlSchema_schema, col.is_xml_document from sys.columns col
left outer join sys.types st on st.user_type_id = col.user_type_id left
outer join
sys.types bt on bt.user_type_id = col.system_type_id
left outer join sys.objects robj on robj.object_id = col.rule_object_id
and robj.type = 'R' left outer join sys.objects dobj on
dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
sys.default_constraints defCst on defCst.parent_object_id = col.object_id
and defCst.parent_column_id = col.column_id left outer join
sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id
= col.column_id left outer join sys.computed_columns cmc on cmc.object_id =
col.object_id and cmc.column_id = col.column_id left outer join
sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
ftc.column_id = col.column_id left outer join sys.xml_schema_collections
xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
col.object_id = object_id(N'dbo.foo') order by col.column_id
Which I whittled down to:
SELECT name,seed_value,increment_value,last_value
FROM sys.identity_columns
WHERE [object_id] = OBJECT_ID('dbo.foo');
The result was:
bar, 1, 1, 5
Notice that seed_value in sys.identity_columns has not changed, though
current_value has.
Aaron Bertrand
SQL Server MVP
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...[vbcol=seagreen]
> Thanks Aaron
> I have tried to run a trace via SQL Profiler though I am having some
> issues
> with the parameters returning more indepth information on where (1,1) is
> stored. Could you advise on what I could include with this for more
> information?
> Morning by the way (evening for me)
> Thanks
> Ads
> Aaaaa
> --
> Thanks
> Aaaaa
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
I am using SQL Server 2000 and I am trying to change the identity seed in a
table. I want to change this via a script. I have used the following command:
DBCC CHECKIDENT (dbo, RESEED, value)
where
dbo = the table in the database whose seed I want to change
value = the value I would like to change the value in Identity seed too.
I get the following result:
"Checking identity information: current identity value '100017140', current
column value '100017148'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Then when I go to the table --> right click --> desigh --> the identity seed
has not changed to what the message above has said it has changed too.
I have tried refreshing the table but still no luck.
Can anyone help. I am looking to have the value to be changed in the design
view for Identity seed.
Thanks.
A
Thanks
Aaaaa
Aaaa
It works just fine (why do you call the table 'dbo'?)
create table test (c int not null identity(1,1))
go
insert into test default values
insert into test default values
insert into test default values
go
select * from test --we have 3 rows
/*
c
1
2
3
*/
go
dbcc checkident (test, RESEED, 1)
insert into test default values
insert into test default values
insert into test default values
select * from test
drop table test
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
> Hi All,
> I am using SQL Server 2000 and I am trying to change the identity seed in
> a
> table. I want to change this via a script. I have used the following
> command:
> DBCC CHECKIDENT (dbo, RESEED, value)
> where
> dbo = the table in the database whose seed I want to change
> value = the value I would like to change the value in Identity seed too.
> I get the following result:
> "Checking identity information: current identity value '100017140',
> current
> column value '100017148'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
> Can anyone help. I am looking to have the value to be changed in the
> design
> view for Identity seed.
> Thanks.
> A
> Thanks
> Aaaaa
|||Hi Uri
Thanks for responding. I only wrote dbo instead of test.
I created a test table like you suggested. Then when you right click on the
test table --> go to design --> at the bottom is a table where the following
is written:
Columns
Description
...
...
Identity Yes
Identity Seed 1 --> this is what i
would like to
Idenitity Increment 1 change, but
the code does not
..... do
so.
.....
I am new to SQL Server 2000 and this forum, I am not sure if I am typing or
checking something wrong.
Please bear with the little knowledge I have.
Thank you so much.
A
Thanks
Aaaaa
"Uri Dimant" wrote:
> Aaaa
> It works just fine (why do you call the table 'dbo'?)
> create table test (c int not null identity(1,1))
> go
> insert into test default values
> insert into test default values
> insert into test default values
> go
> select * from test --we have 3 rows
> /*
> c
> --
> 1
> 2
> 3
> */
> go
> dbcc checkident (test, RESEED, 1)
> insert into test default values
> insert into test default values
> insert into test default values
> select * from test
> drop table test
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
>
>
|||> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
Did you try inserting a row into the table? Did you try closing and
re-opening Management Studio?
Aaron Bertrand
SQL Server MVP
|||> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
I inserted a new table, shut down the application and opened it up. When it
opened the table had been updated (test --> right click --> open table -->
return all rows)
But then when I check design --> column --> identity seed the value has not
changed.
This is where the problem lies.
A
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
> --
> Aaron Bertrand
> SQL Server MVP
>
>
|||On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
<Aaaaa@.discussions.microsoft.com> wrote:
>I inserted a new table, shut down the application and opened it up. When it
>opened the table had been updated (test --> right click --> open table -->
>return all rows)
>But then when I check design --> column --> identity seed the value has not
>changed.
>This is where the problem lies.
Be sure to right-click on the table in the tree and choose REFRESH.
Roy Harvey
Beacon Falls, CT
|||Hi
I definately have refreshed each time. Still no joy. It seems to update
the table but not the design view.
A
Thanks
Aaaaa
"Roy Harvey" wrote:
> On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
> <Aaaaa@.discussions.microsoft.com> wrote:
>
> Be sure to right-click on the table in the tree and choose REFRESH.
> Roy Harvey
> Beacon Falls, CT
>
|||> Be sure to right-click on the table in the tree and choose REFRESH.
No, this really is a bug in the table designer. I can reproduce it in 2000,
2005 and even in Katmai. There doesn't seem to be a way to make the table
designer reflect numerous changes to the identity seed value...
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
(The question, of course, is where on earth does SQL Server store the 1,1
from initial creation? They must be coming from somewhere. Yes, I'm too
lazy this morning to fire up profiler.)
Aaron Bertrand
SQL Server MVP
|||Thanks Aaron
I have tried to run a trace via SQL Profiler though I am having some issues
with the parameters returning more indepth information on where (1,1) is
stored. Could you advise on what I could include with this for more
information?
Morning by the way (evening for me)
Thanks
Ads
Aaaaa
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> No, this really is a bug in the table designer. I can reproduce it in 2000,
> 2005 and even in Katmai. There doesn't seem to be a way to make the table
> designer reflect numerous changes to the identity seed value...
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
> (The question, of course, is where on earth does SQL Server store the 1,1
> from initial creation? They must be coming from somewhere. Yes, I'm too
> lazy this morning to fire up profiler.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>
|||In SQL 2008, I picked this up from TSQL:StmtCompleted:
select col.name, col.column_id,
st.name as DT_name,
schema_name(st.schema_id) as DT_schema,
col.max_length, col.precision, col.scale, bt.name as BT_name,
col.collation_name, col.is_nullable, col.is_ansi_padded,
col.is_rowguidcol, col.is_identity,
case when(idc.column_id is null)
then null else CONVERT(nvarchar(40), idc.seed_value) end,
case when(idc.column_id is null) then null
else CONVERT(nvarchar(40), idc.increment_value) end,
CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
as is_computed,
convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
as IsIdNotForRepl,
col.is_replicated,
col.is_non_sql_subscribed, col.is_merge_published,
col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
is_FullTextCol,
col_name(col.object_id, ftc.type_column_id) FT_type_column,
ftc.language_id as FT_language_id,
case when(cmc.column_id is null) then null else cmc.definition end as
formular,
case when(cmc.column_id is null) then null else cmc.is_persisted end as
is_persisted,
defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
'IsDeterministic')
as IsDeterministic, xmlcoll.name as xmlSchema_name,
schema_name(xmlcoll.schema_id)
as xmlSchema_schema, col.is_xml_document from sys.columns col
left outer join sys.types st on st.user_type_id = col.user_type_id left
outer join
sys.types bt on bt.user_type_id = col.system_type_id
left outer join sys.objects robj on robj.object_id = col.rule_object_id
and robj.type = 'R' left outer join sys.objects dobj on
dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
sys.default_constraints defCst on defCst.parent_object_id = col.object_id
and defCst.parent_column_id = col.column_id left outer join
sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id
= col.column_id left outer join sys.computed_columns cmc on cmc.object_id =
col.object_id and cmc.column_id = col.column_id left outer join
sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
ftc.column_id = col.column_id left outer join sys.xml_schema_collections
xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
col.object_id = object_id(N'dbo.foo') order by col.column_id
Which I whittled down to:
SELECT name,seed_value,increment_value,last_value
FROM sys.identity_columns
WHERE [object_id] = OBJECT_ID('dbo.foo');
The result was:
bar, 1, 1, 5
Notice that seed_value in sys.identity_columns has not changed, though
current_value has.
Aaron Bertrand
SQL Server MVP
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...[vbcol=seagreen]
> Thanks Aaron
> I have tried to run a trace via SQL Profiler though I am having some
> issues
> with the parameters returning more indepth information on where (1,1) is
> stored. Could you advise on what I could include with this for more
> information?
> Morning by the way (evening for me)
> Thanks
> Ads
> Aaaaa
> --
> Thanks
> Aaaaa
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
Changing Identity Seed
Hi All,
I am using SQL Server 2000 and I am trying to change the identity seed in a
table. I want to change this via a script. I have used the following command
:
DBCC CHECKIDENT (dbo, RESEED, value)
where
dbo = the table in the database whose seed I want to change
value = the value I would like to change the value in Identity seed too.
I get the following result:
"Checking identity information: current identity value '100017140', current
column value '100017148'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Then when I go to the table --> right click --> desigh --> the identity seed
has not changed to what the message above has said it has changed too.
I have tried refreshing the table but still no luck.
Can anyone help. I am looking to have the value to be changed in the design
view for Identity seed.
Thanks.
A
Thanks
AaaaaAaaa
It works just fine (why do you call the table 'dbo'?)
create table test (c int not null identity(1,1))
go
insert into test default values
insert into test default values
insert into test default values
go
select * from test --we have 3 rows
/*
c
--
1
2
3
*/
go
dbcc checkident (test, RESEED, 1)
insert into test default values
insert into test default values
insert into test default values
select * from test
drop table test
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
> Hi All,
> I am using SQL Server 2000 and I am trying to change the identity seed in
> a
> table. I want to change this via a script. I have used the following
> command:
> DBCC CHECKIDENT (dbo, RESEED, value)
> where
> dbo = the table in the database whose seed I want to change
> value = the value I would like to change the value in Identity seed too.
> I get the following result:
> "Checking identity information: current identity value '100017140',
> current
> column value '100017148'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
> Can anyone help. I am looking to have the value to be changed in the
> design
> view for Identity seed.
> Thanks.
> A
> Thanks
> Aaaaa|||Hi Uri
Thanks for responding. I only wrote dbo instead of test.
I created a test table like you suggested. Then when you right click on the
test table --> go to design --> at the bottom is a table where the followin
g
is written:
Columns
Description
...
..
Identity Yes
Identity Seed 1 --> this is what i
would like to
Idenitity Increment 1 change, but
the code does not
.... d
o
so.
....
I am new to SQL Server 2000 and this forum, I am not sure if I am typing or
checking something wrong.
Please bear with the little knowledge I have.
Thank you so much.
A
Thanks
Aaaaa
"Uri Dimant" wrote:
> Aaaa
> It works just fine (why do you call the table 'dbo'?)
> create table test (c int not null identity(1,1))
> go
> insert into test default values
> insert into test default values
> insert into test default values
> go
> select * from test --we have 3 rows
> /*
> c
> --
> 1
> 2
> 3
> */
> go
> dbcc checkident (test, RESEED, 1)
> insert into test default values
> insert into test default values
> insert into test default values
> select * from test
> drop table test
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
>
>|||> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
Did you try inserting a row into the table? Did you try closing and
re-opening Management Studio?
Aaron Bertrand
SQL Server MVP|||> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
I inserted a new table, shut down the application and opened it up. When it
opened the table had been updated (test --> right click --> open table -->
return all rows)
But then when I check design --> column --> identity seed the value has not
changed.
This is where the problem lies.
A
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
<Aaaaa@.discussions.microsoft.com> wrote:
>I inserted a new table, shut down the application and opened it up. When i
t
>opened the table had been updated (test --> right click --> open table -->
>return all rows)
>But then when I check design --> column --> identity seed the value has not
>changed.
>This is where the problem lies.
Be sure to right-click on the table in the tree and choose REFRESH.
Roy Harvey
Beacon Falls, CT|||Thanks Aaron
I have tried to run a trace via SQL Profiler though I am having some issues
with the parameters returning more indepth information on where (1,1) is
stored. Could you advise on what I could include with this for more
information?
Morning by the way (evening for me)
Thanks
Ads
Aaaaa
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> No, this really is a bug in the table designer. I can reproduce it in 200
0,
> 2005 and even in Katmai. There doesn't seem to be a way to make the table
> designer reflect numerous changes to the identity seed value...
> http://connect.microsoft.com/SQLSer...=289
675
> (The question, of course, is where on earth does SQL Server store the 1,1
> from initial creation? They must be coming from somewhere. Yes, I'm too
> lazy this morning to fire up profiler.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||In SQL 2008, I picked this up from TSQL:StmtCompleted:
select col.name, col.column_id,
st.name as DT_name,
schema_name(st.schema_id) as DT_schema,
col.max_length, col.precision, col.scale, bt.name as BT_name,
col.collation_name, col.is_nullable, col.is_ansi_padded,
col.is_rowguidcol, col.is_identity,
case when(idc.column_id is null)
then null else CONVERT(nvarchar(40), idc.seed_value) end,
case when(idc.column_id is null) then null
else CONVERT(nvarchar(40), idc.increment_value) end,
CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
as is_computed,
convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
as IsIdNotForRepl,
col.is_replicated,
col.is_non_sql_subscribed, col.is_merge_published,
col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
is_FullTextCol,
col_name(col.object_id, ftc.type_column_id) FT_type_column,
ftc.language_id as FT_language_id,
case when(cmc.column_id is null) then null else cmc.definition end as
formular,
case when(cmc.column_id is null) then null else cmc.is_persisted end as
is_persisted,
defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
'IsDeterministic')
as IsDeterministic, xmlcoll.name as xmlSchema_name,
schema_name(xmlcoll.schema_id)
as xmlSchema_schema, col.is_xml_document from sys.columns col
left outer join sys.types st on st.user_type_id = col.user_type_id left
outer join
sys.types bt on bt.user_type_id = col.system_type_id
left outer join sys.objects robj on robj.object_id = col.rule_object_id
and robj.type = 'R' left outer join sys.objects dobj on
dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
sys.default_constraints defCst on defCst.parent_object_id = col.object_id
and defCst.parent_column_id = col.column_id left outer join
sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id
= col.column_id left outer join sys.computed_columns cmc on cmc.object_id =
col.object_id and cmc.column_id = col.column_id left outer join
sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
ftc.column_id = col.column_id left outer join sys.xml_schema_collections
xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
col.object_id = object_id(N'dbo.foo') order by col.column_id
Which I whittled down to:
SELECT name,seed_value,increment_value,last_val
ue
FROM sys.identity_columns
WHERE [object_id] = OBJECT_ID('dbo.foo');
The result was:
bar, 1, 1, 5
Notice that seed_value in sys.identity_columns has not changed, though
current_value has.
Aaron Bertrand
SQL Server MVP
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...[vbcol=seagreen]
> Thanks Aaron
> I have tried to run a trace via SQL Profiler though I am having some
> issues
> with the parameters returning more indepth information on where (1,1) is
> stored. Could you advise on what I could include with this for more
> information?
> Morning by the way (evening for me)
> Thanks
> Ads
> Aaaaa
> --
> Thanks
> Aaaaa
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||That was very well shrunk BUT i still failed miserably and this code did not
work. Too many errors,
> SELECT name,seed_value,increment_value,last_val
ue
> FROM sys.identity_columns
> WHERE [object_id] = OBJECT_ID('dbo.foo');
The system did not like 'sys.identity_columns' for a start.
Sorry about bugging you on this. This bug is really bugging me.
Would the first line be formatted like this?
SELECT test,24,1,21
Ads
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> In SQL 2008, I picked this up from TSQL:StmtCompleted:
> select col.name, col.column_id,
> st.name as DT_name,
> schema_name(st.schema_id) as DT_schema,
> col.max_length, col.precision, col.scale, bt.name as BT_name,
> col.collation_name, col.is_nullable, col.is_ansi_padded,
> col.is_rowguidcol, col.is_identity,
> case when(idc.column_id is null)
> then null else CONVERT(nvarchar(40), idc.seed_value) end,
> case when(idc.column_id is null) then null
> else CONVERT(nvarchar(40), idc.increment_value) end,
> CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
> as is_computed,
> convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
> as IsIdNotForRepl,
> col.is_replicated,
> col.is_non_sql_subscribed, col.is_merge_published,
> col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
> schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
> OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
> dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
> CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
> is_FullTextCol,
> col_name(col.object_id, ftc.type_column_id) FT_type_column,
> ftc.language_id as FT_language_id,
> case when(cmc.column_id is null) then null else cmc.definition end as
> formular,
> case when(cmc.column_id is null) then null else cmc.is_persisted end as
> is_persisted,
> defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
> 'IsDeterministic')
> as IsDeterministic, xmlcoll.name as xmlSchema_name,
> schema_name(xmlcoll.schema_id)
> as xmlSchema_schema, col.is_xml_document from sys.columns col
> left outer join sys.types st on st.user_type_id = col.user_type_id left
> outer join
> sys.types bt on bt.user_type_id = col.system_type_id
> left outer join sys.objects robj on robj.object_id = col.rule_object_id
> and robj.type = 'R' left outer join sys.objects dobj on
> dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
> sys.default_constraints defCst on defCst.parent_object_id = col.object_id
> and defCst.parent_column_id = col.column_id left outer join
> sys.identity_columns idc on idc.object_id = col.object_id and idc.column_i
d
> = col.column_id left outer join sys.computed_columns cmc on cmc.object_id
=
> col.object_id and cmc.column_id = col.column_id left outer join
> sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
> ftc.column_id = col.column_id left outer join sys.xml_schema_collections
> xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
> col.object_id = object_id(N'dbo.foo') order by col.column_id
> Which I whittled down to:
> SELECT name,seed_value,increment_value,last_val
ue
> FROM sys.identity_columns
> WHERE [object_id] = OBJECT_ID('dbo.foo');
> The result was:
> bar, 1, 1, 5
> Notice that seed_value in sys.identity_columns has not changed, though
> current_value has.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...
>
>|||> That was very well shrunk BUT i still failed miserably and this code did
> not
> work. Too many errors,
I know, as I stated, this was for SQL 2005/2008, not for 2000. You will
have to turn Profiler on, including TSQL:StmtCompleted, and then open the
table in design view. There will be a bunch of statements there, and one of
them will include something about identity / seed.
Aaron Bertrand
SQL Server MVP
I am using SQL Server 2000 and I am trying to change the identity seed in a
table. I want to change this via a script. I have used the following command
:
DBCC CHECKIDENT (dbo, RESEED, value)
where
dbo = the table in the database whose seed I want to change
value = the value I would like to change the value in Identity seed too.
I get the following result:
"Checking identity information: current identity value '100017140', current
column value '100017148'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Then when I go to the table --> right click --> desigh --> the identity seed
has not changed to what the message above has said it has changed too.
I have tried refreshing the table but still no luck.
Can anyone help. I am looking to have the value to be changed in the design
view for Identity seed.
Thanks.
A
Thanks
AaaaaAaaa
It works just fine (why do you call the table 'dbo'?)
create table test (c int not null identity(1,1))
go
insert into test default values
insert into test default values
insert into test default values
go
select * from test --we have 3 rows
/*
c
--
1
2
3
*/
go
dbcc checkident (test, RESEED, 1)
insert into test default values
insert into test default values
insert into test default values
select * from test
drop table test
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
> Hi All,
> I am using SQL Server 2000 and I am trying to change the identity seed in
> a
> table. I want to change this via a script. I have used the following
> command:
> DBCC CHECKIDENT (dbo, RESEED, value)
> where
> dbo = the table in the database whose seed I want to change
> value = the value I would like to change the value in Identity seed too.
> I get the following result:
> "Checking identity information: current identity value '100017140',
> current
> column value '100017148'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
> Can anyone help. I am looking to have the value to be changed in the
> design
> view for Identity seed.
> Thanks.
> A
> Thanks
> Aaaaa|||Hi Uri
Thanks for responding. I only wrote dbo instead of test.
I created a test table like you suggested. Then when you right click on the
test table --> go to design --> at the bottom is a table where the followin
g
is written:
Columns
Description
...
..
Identity Yes
Identity Seed 1 --> this is what i
would like to
Idenitity Increment 1 change, but
the code does not
.... d
o
so.
....
I am new to SQL Server 2000 and this forum, I am not sure if I am typing or
checking something wrong.
Please bear with the little knowledge I have.
Thank you so much.
A
Thanks
Aaaaa
"Uri Dimant" wrote:
> Aaaa
> It works just fine (why do you call the table 'dbo'?)
> create table test (c int not null identity(1,1))
> go
> insert into test default values
> insert into test default values
> insert into test default values
> go
> select * from test --we have 3 rows
> /*
> c
> --
> 1
> 2
> 3
> */
> go
> dbcc checkident (test, RESEED, 1)
> insert into test default values
> insert into test default values
> insert into test default values
> select * from test
> drop table test
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
>
>|||> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
Did you try inserting a row into the table? Did you try closing and
re-opening Management Studio?
Aaron Bertrand
SQL Server MVP|||> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
I inserted a new table, shut down the application and opened it up. When it
opened the table had been updated (test --> right click --> open table -->
return all rows)
But then when I check design --> column --> identity seed the value has not
changed.
This is where the problem lies.
A
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
<Aaaaa@.discussions.microsoft.com> wrote:
>I inserted a new table, shut down the application and opened it up. When i
t
>opened the table had been updated (test --> right click --> open table -->
>return all rows)
>But then when I check design --> column --> identity seed the value has not
>changed.
>This is where the problem lies.
Be sure to right-click on the table in the tree and choose REFRESH.
Roy Harvey
Beacon Falls, CT|||Thanks Aaron
I have tried to run a trace via SQL Profiler though I am having some issues
with the parameters returning more indepth information on where (1,1) is
stored. Could you advise on what I could include with this for more
information?
Morning by the way (evening for me)
Thanks
Ads
Aaaaa
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> No, this really is a bug in the table designer. I can reproduce it in 200
0,
> 2005 and even in Katmai. There doesn't seem to be a way to make the table
> designer reflect numerous changes to the identity seed value...
> http://connect.microsoft.com/SQLSer...=289
675
> (The question, of course, is where on earth does SQL Server store the 1,1
> from initial creation? They must be coming from somewhere. Yes, I'm too
> lazy this morning to fire up profiler.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||In SQL 2008, I picked this up from TSQL:StmtCompleted:
select col.name, col.column_id,
st.name as DT_name,
schema_name(st.schema_id) as DT_schema,
col.max_length, col.precision, col.scale, bt.name as BT_name,
col.collation_name, col.is_nullable, col.is_ansi_padded,
col.is_rowguidcol, col.is_identity,
case when(idc.column_id is null)
then null else CONVERT(nvarchar(40), idc.seed_value) end,
case when(idc.column_id is null) then null
else CONVERT(nvarchar(40), idc.increment_value) end,
CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
as is_computed,
convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
as IsIdNotForRepl,
col.is_replicated,
col.is_non_sql_subscribed, col.is_merge_published,
col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
is_FullTextCol,
col_name(col.object_id, ftc.type_column_id) FT_type_column,
ftc.language_id as FT_language_id,
case when(cmc.column_id is null) then null else cmc.definition end as
formular,
case when(cmc.column_id is null) then null else cmc.is_persisted end as
is_persisted,
defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
'IsDeterministic')
as IsDeterministic, xmlcoll.name as xmlSchema_name,
schema_name(xmlcoll.schema_id)
as xmlSchema_schema, col.is_xml_document from sys.columns col
left outer join sys.types st on st.user_type_id = col.user_type_id left
outer join
sys.types bt on bt.user_type_id = col.system_type_id
left outer join sys.objects robj on robj.object_id = col.rule_object_id
and robj.type = 'R' left outer join sys.objects dobj on
dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
sys.default_constraints defCst on defCst.parent_object_id = col.object_id
and defCst.parent_column_id = col.column_id left outer join
sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id
= col.column_id left outer join sys.computed_columns cmc on cmc.object_id =
col.object_id and cmc.column_id = col.column_id left outer join
sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
ftc.column_id = col.column_id left outer join sys.xml_schema_collections
xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
col.object_id = object_id(N'dbo.foo') order by col.column_id
Which I whittled down to:
SELECT name,seed_value,increment_value,last_val
ue
FROM sys.identity_columns
WHERE [object_id] = OBJECT_ID('dbo.foo');
The result was:
bar, 1, 1, 5
Notice that seed_value in sys.identity_columns has not changed, though
current_value has.
Aaron Bertrand
SQL Server MVP
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...[vbcol=seagreen]
> Thanks Aaron
> I have tried to run a trace via SQL Profiler though I am having some
> issues
> with the parameters returning more indepth information on where (1,1) is
> stored. Could you advise on what I could include with this for more
> information?
> Morning by the way (evening for me)
> Thanks
> Ads
> Aaaaa
> --
> Thanks
> Aaaaa
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||That was very well shrunk BUT i still failed miserably and this code did not
work. Too many errors,
> SELECT name,seed_value,increment_value,last_val
ue
> FROM sys.identity_columns
> WHERE [object_id] = OBJECT_ID('dbo.foo');
The system did not like 'sys.identity_columns' for a start.
Sorry about bugging you on this. This bug is really bugging me.
Would the first line be formatted like this?
SELECT test,24,1,21
Ads
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> In SQL 2008, I picked this up from TSQL:StmtCompleted:
> select col.name, col.column_id,
> st.name as DT_name,
> schema_name(st.schema_id) as DT_schema,
> col.max_length, col.precision, col.scale, bt.name as BT_name,
> col.collation_name, col.is_nullable, col.is_ansi_padded,
> col.is_rowguidcol, col.is_identity,
> case when(idc.column_id is null)
> then null else CONVERT(nvarchar(40), idc.seed_value) end,
> case when(idc.column_id is null) then null
> else CONVERT(nvarchar(40), idc.increment_value) end,
> CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
> as is_computed,
> convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
> as IsIdNotForRepl,
> col.is_replicated,
> col.is_non_sql_subscribed, col.is_merge_published,
> col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
> schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
> OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
> dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
> CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
> is_FullTextCol,
> col_name(col.object_id, ftc.type_column_id) FT_type_column,
> ftc.language_id as FT_language_id,
> case when(cmc.column_id is null) then null else cmc.definition end as
> formular,
> case when(cmc.column_id is null) then null else cmc.is_persisted end as
> is_persisted,
> defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
> 'IsDeterministic')
> as IsDeterministic, xmlcoll.name as xmlSchema_name,
> schema_name(xmlcoll.schema_id)
> as xmlSchema_schema, col.is_xml_document from sys.columns col
> left outer join sys.types st on st.user_type_id = col.user_type_id left
> outer join
> sys.types bt on bt.user_type_id = col.system_type_id
> left outer join sys.objects robj on robj.object_id = col.rule_object_id
> and robj.type = 'R' left outer join sys.objects dobj on
> dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
> sys.default_constraints defCst on defCst.parent_object_id = col.object_id
> and defCst.parent_column_id = col.column_id left outer join
> sys.identity_columns idc on idc.object_id = col.object_id and idc.column_i
d
> = col.column_id left outer join sys.computed_columns cmc on cmc.object_id
=
> col.object_id and cmc.column_id = col.column_id left outer join
> sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
> ftc.column_id = col.column_id left outer join sys.xml_schema_collections
> xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
> col.object_id = object_id(N'dbo.foo') order by col.column_id
> Which I whittled down to:
> SELECT name,seed_value,increment_value,last_val
ue
> FROM sys.identity_columns
> WHERE [object_id] = OBJECT_ID('dbo.foo');
> The result was:
> bar, 1, 1, 5
> Notice that seed_value in sys.identity_columns has not changed, though
> current_value has.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...
>
>|||> That was very well shrunk BUT i still failed miserably and this code did
> not
> work. Too many errors,
I know, as I stated, this was for SQL 2005/2008, not for 2000. You will
have to turn Profiler on, including TSQL:StmtCompleted, and then open the
table in design view. There will be a bunch of statements there, and one of
them will include something about identity / seed.
Aaron Bertrand
SQL Server MVP
Thursday, February 16, 2012
Changing file names using xp_cmdshell
My goal is to run a script like the one below but for every time I run it I get a new year. For instance if I run it this year I'll get 2004.txt for my file name and next year I'll get 2005.txt for my file name. Is this possible? If so any suggestions.
declare @.firstyr int,
@.var sysname,
@.cmd sysname
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by OrderDate desc"' , no_outpu
t
You can do that with dynamic sql, something like this:
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
--set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
+ rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"D" <D@.discussions.microsoft.com> wrote in message
news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> My goal is to run a script like the one below but for every time I run it
I get a new year. For instance if I run it this year I'll get 2004.txt for
my file name and next year I'll get 2005.txt for my file name. Is this
possible? If so any suggestions.
> declare @.firstyr int,
> @.var sysname,
> @.cmd sysname
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set
nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"' , no_output
>
>
|||That worked. Thanks for your help. Final example below>>>
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -E -o ' + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by OrderDa
te desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
"Gregory A. Larsen" wrote:
> You can do that with dynamic sql, something like this:
> declare @.firstyr int,
> @.var varchar(1000),
> @.cmd varchar(1000),
> @.dcmd varchar(1000)
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> --set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
> + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
> OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
> O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
> ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"'' , no_output'
> print @.dcmd
> exec (@.dcmd)
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "D" <D@.discussions.microsoft.com> wrote in message
> news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> I get a new year. For instance if I run it this year I'll get 2004.txt for
> my file name and next year I'll get 2005.txt for my file name. Is this
> possible? If so any suggestions.
> nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"' , no_output
>
>
declare @.firstyr int,
@.var sysname,
@.cmd sysname
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by OrderDate desc"' , no_outpu
t
You can do that with dynamic sql, something like this:
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
--set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
+ rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"D" <D@.discussions.microsoft.com> wrote in message
news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> My goal is to run a script like the one below but for every time I run it
I get a new year. For instance if I run it this year I'll get 2004.txt for
my file name and next year I'll get 2005.txt for my file name. Is this
possible? If so any suggestions.
> declare @.firstyr int,
> @.var sysname,
> @.cmd sysname
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set
nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"' , no_output
>
>
|||That worked. Thanks for your help. Final example below>>>
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -E -o ' + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by OrderDa
te desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
"Gregory A. Larsen" wrote:
> You can do that with dynamic sql, something like this:
> declare @.firstyr int,
> @.var varchar(1000),
> @.cmd varchar(1000),
> @.dcmd varchar(1000)
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> --set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
> + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
> OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
> O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
> ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"'' , no_output'
> print @.dcmd
> exec (@.dcmd)
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "D" <D@.discussions.microsoft.com> wrote in message
> news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> I get a new year. For instance if I run it this year I'll get 2004.txt for
> my file name and next year I'll get 2005.txt for my file name. Is this
> possible? If so any suggestions.
> nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"' , no_output
>
>
Changing file names using xp_cmdshell
My goal is to run a script like the one below but for every time I run it I
get a new year. For instance if I run it this year I'll get 2004.txt for my
file name and next year I'll get 2005.txt for my file name. Is this possib
le? If so any suggestions.
declare @.firstyr int,
@.var sysname,
@.cmd sysname
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set noco
unt on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orde
rs O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by
OrderDate desc"' , no_outpu
tYou can do that with dynamic sql, something like this:
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
--set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
+ rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"D" <D@.discussions.microsoft.com> wrote in message
news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> My goal is to run a script like the one below but for every time I run it
I get a new year. For instance if I run it this year I'll get 2004.txt for
my file name and next year I'll get 2005.txt for my file name. Is this
possible? If so any suggestions.
> declare @.firstyr int,
> @.var sysname,
> @.cmd sysname
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set
nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"' , no_output
>
>|||That worked. Thanks for your help. Final example below>>>
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -E -o ' + rtrim(@.c
md) + ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate fro
m Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.
CustomerId order by OrderDa
te desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
"Gregory A. Larsen" wrote:
> You can do that with dynamic sql, something like this:
> declare @.firstyr int,
> @.var varchar(1000),
> @.cmd varchar(1000),
> @.dcmd varchar(1000)
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> --set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o
'
> + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
> OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
> O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd)
+
> ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"'' , no_output'
> print @.dcmd
> exec (@.dcmd)
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "D" <D@.discussions.microsoft.com> wrote in message
> news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> I get a new year. For instance if I run it this year I'll get 2004.txt fo
r
> my file name and next year I'll get 2005.txt for my file name. Is this
> possible? If so any suggestions.
> nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"' , no_output
>
>
get a new year. For instance if I run it this year I'll get 2004.txt for my
file name and next year I'll get 2005.txt for my file name. Is this possib
le? If so any suggestions.
declare @.firstyr int,
@.var sysname,
@.cmd sysname
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set noco
unt on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orde
rs O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by
OrderDate desc"' , no_outpu
tYou can do that with dynamic sql, something like this:
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
--set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
+ rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"D" <D@.discussions.microsoft.com> wrote in message
news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> My goal is to run a script like the one below but for every time I run it
I get a new year. For instance if I run it this year I'll get 2004.txt for
my file name and next year I'll get 2005.txt for my file name. Is this
possible? If so any suggestions.
> declare @.firstyr int,
> @.var sysname,
> @.cmd sysname
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set
nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"' , no_output
>
>|||That worked. Thanks for your help. Final example below>>>
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -E -o ' + rtrim(@.c
md) + ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate fro
m Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.
CustomerId order by OrderDa
te desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
"Gregory A. Larsen" wrote:
> You can do that with dynamic sql, something like this:
> declare @.firstyr int,
> @.var varchar(1000),
> @.cmd varchar(1000),
> @.dcmd varchar(1000)
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> --set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o
'
> + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
> OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
> O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd)
+
> ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"'' , no_output'
> print @.dcmd
> exec (@.dcmd)
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "D" <D@.discussions.microsoft.com> wrote in message
> news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> I get a new year. For instance if I run it this year I'll get 2004.txt fo
r
> my file name and next year I'll get 2005.txt for my file name. Is this
> possible? If so any suggestions.
> nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"' , no_output
>
>
Changing file grouth automaticly
Hello there
Is there a way to build sql script that changes all the file growth of all
the databases in the current server?Roy Goldhammer a crit :
> Hello there
> Is there a way to build sql script that changes all the file growth of all
> the databases in the current server?
>
use the hidden procedure sp_MSforeachdb and inside the string use the
character ? in place of db name.
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Whell SQLPro
I can't find any help on this store procedure.
Can you give me the exact syntax for using sp_MSforeachdb?
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:%23tHAQnDTGHA.5172@.TK2MSFTNGP12.phx.gbl...
> Roy Goldhammer a crit :
> use the hidden procedure sp_MSforeachdb and inside the string use the
> character ? in place of db name.
> A +
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||It's actually not hidden, and can be found in the master database. However,
it is not documented (at least not officially).
http://www.dbazine.com/sql/sql-articles/larsen5
http://groups.google.com/groups?q=sp_MSforeachdb&hl=en
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eXRt70DTGHA.2088@.TK2MSFTNGP14.phx.gbl...
> Whell SQLPro
> I can't find any help on this store procedure.
> Can you give me the exact syntax for using sp_MSforeachdb?
> "SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
> news:%23tHAQnDTGHA.5172@.TK2MSFTNGP12.phx.gbl...
>
Is there a way to build sql script that changes all the file growth of all
the databases in the current server?Roy Goldhammer a crit :
> Hello there
> Is there a way to build sql script that changes all the file growth of all
> the databases in the current server?
>
use the hidden procedure sp_MSforeachdb and inside the string use the
character ? in place of db name.
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Whell SQLPro
I can't find any help on this store procedure.
Can you give me the exact syntax for using sp_MSforeachdb?
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:%23tHAQnDTGHA.5172@.TK2MSFTNGP12.phx.gbl...
> Roy Goldhammer a crit :
> use the hidden procedure sp_MSforeachdb and inside the string use the
> character ? in place of db name.
> A +
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||It's actually not hidden, and can be found in the master database. However,
it is not documented (at least not officially).
http://www.dbazine.com/sql/sql-articles/larsen5
http://groups.google.com/groups?q=sp_MSforeachdb&hl=en
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eXRt70DTGHA.2088@.TK2MSFTNGP14.phx.gbl...
> Whell SQLPro
> I can't find any help on this store procedure.
> Can you give me the exact syntax for using sp_MSforeachdb?
> "SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
> news:%23tHAQnDTGHA.5172@.TK2MSFTNGP12.phx.gbl...
>
Tuesday, February 14, 2012
Changing DB with a script using a variable.
I am trying to write a TSQL Script for maintenance that willl read the
databases from sysdatabases and change to each database inturn to perform
some maintenance tasks.
Problem is I can't get SQL to change to the next database.
Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
change the database.
I know there has to be a way... any ideas?
Has to be available in TSQL as this will become a Stored Procedure when
finished.
Raymond Laubert
MCSE, MCDBA, MCT
You can create your maintenance task as a SP, and then you can execute the
following
EXEC dbo.sp_MSforeachdb @.command1 = "use ? exec <your spname>" But this one
executes on the system dbs also.
The other way is to include the following in your T-SQL:
'USE [' + @.dbname + ']' + char(13) +'Go'
This should work i believe..I used it long time somewhere...
thks,
Manikanth
"Ray Laubert" <ray@.rsl-webhosting.com> wrote in message
news:58B901C8-4603-4B6E-B8AF-C6DBEDD50EFC@.microsoft.com...
>I am trying to write a TSQL Script for maintenance that willl read the
> databases from sysdatabases and change to each database inturn to perform
> some maintenance tasks.
> Problem is I can't get SQL to change to the next database.
> Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
> change the database.
> I know there has to be a way... any ideas?
> Has to be available in TSQL as this will become a Stored Procedure when
> finished.
>
> --
> Raymond Laubert
> MCSE, MCDBA, MCT
databases from sysdatabases and change to each database inturn to perform
some maintenance tasks.
Problem is I can't get SQL to change to the next database.
Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
change the database.
I know there has to be a way... any ideas?
Has to be available in TSQL as this will become a Stored Procedure when
finished.
Raymond Laubert
MCSE, MCDBA, MCT
You can create your maintenance task as a SP, and then you can execute the
following
EXEC dbo.sp_MSforeachdb @.command1 = "use ? exec <your spname>" But this one
executes on the system dbs also.
The other way is to include the following in your T-SQL:
'USE [' + @.dbname + ']' + char(13) +'Go'
This should work i believe..I used it long time somewhere...
thks,
Manikanth
"Ray Laubert" <ray@.rsl-webhosting.com> wrote in message
news:58B901C8-4603-4B6E-B8AF-C6DBEDD50EFC@.microsoft.com...
>I am trying to write a TSQL Script for maintenance that willl read the
> databases from sysdatabases and change to each database inturn to perform
> some maintenance tasks.
> Problem is I can't get SQL to change to the next database.
> Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
> change the database.
> I know there has to be a way... any ideas?
> Has to be available in TSQL as this will become a Stored Procedure when
> finished.
>
> --
> Raymond Laubert
> MCSE, MCDBA, MCT
Changing DB with a script using a variable.
I am trying to write a TSQL Script for maintenance that willl read the
databases from sysdatabases and change to each database inturn to perform
some maintenance tasks.
Problem is I can't get SQL to change to the next database.
Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
change the database.
I know there has to be a way... any ideas?
Has to be available in TSQL as this will become a Stored Procedure when
finished.
Raymond Laubert
MCSE, MCDBA, MCTYou can create your maintenance task as a SP, and then you can execute the
following
EXEC dbo.sp_MSforeachdb @.command1 = "use ? exec <your spname>" But this one
executes on the system dbs also.
The other way is to include the following in your T-SQL:
'USE [' + @.dbname + ']' + char(13) +'Go'
This should work i believe..I used it long time somewhere...
thks,
Manikanth
"Ray Laubert" <ray@.rsl-webhosting.com> wrote in message
news:58B901C8-4603-4B6E-B8AF-C6DBEDD50EFC@.microsoft.com...
>I am trying to write a TSQL Script for maintenance that willl read the
> databases from sysdatabases and change to each database inturn to perform
> some maintenance tasks.
> Problem is I can't get SQL to change to the next database.
> Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
> change the database.
> I know there has to be a way... any ideas?
> Has to be available in TSQL as this will become a Stored Procedure when
> finished.
>
> --
> Raymond Laubert
> MCSE, MCDBA, MCT|||Hi,
the problem with your command is, that EXEC will open a connection, change
the database on close the connection again. EXEC will use another scope than
the actual script is running in. As Manikanth said, you will have to include
the Script within your EXEC call to execute it on the same execution
context.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"Ray Laubert" <ray@.rsl-webhosting.com> wrote in message
news:58B901C8-4603-4B6E-B8AF-C6DBEDD50EFC@.microsoft.com...
>I am trying to write a TSQL Script for maintenance that willl read the
> databases from sysdatabases and change to each database inturn to perform
> some maintenance tasks.
> Problem is I can't get SQL to change to the next database.
> Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
> change the database.
> I know there has to be a way... any ideas?
> Has to be available in TSQL as this will become a Stored Procedure when
> finished.
>
> --
> Raymond Laubert
> MCSE, MCDBA, MCT
>
databases from sysdatabases and change to each database inturn to perform
some maintenance tasks.
Problem is I can't get SQL to change to the next database.
Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
change the database.
I know there has to be a way... any ideas?
Has to be available in TSQL as this will become a Stored Procedure when
finished.
Raymond Laubert
MCSE, MCDBA, MCTYou can create your maintenance task as a SP, and then you can execute the
following
EXEC dbo.sp_MSforeachdb @.command1 = "use ? exec <your spname>" But this one
executes on the system dbs also.
The other way is to include the following in your T-SQL:
'USE [' + @.dbname + ']' + char(13) +'Go'
This should work i believe..I used it long time somewhere...
thks,
Manikanth
"Ray Laubert" <ray@.rsl-webhosting.com> wrote in message
news:58B901C8-4603-4B6E-B8AF-C6DBEDD50EFC@.microsoft.com...
>I am trying to write a TSQL Script for maintenance that willl read the
> databases from sysdatabases and change to each database inturn to perform
> some maintenance tasks.
> Problem is I can't get SQL to change to the next database.
> Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
> change the database.
> I know there has to be a way... any ideas?
> Has to be available in TSQL as this will become a Stored Procedure when
> finished.
>
> --
> Raymond Laubert
> MCSE, MCDBA, MCT|||Hi,
the problem with your command is, that EXEC will open a connection, change
the database on close the connection again. EXEC will use another scope than
the actual script is running in. As Manikanth said, you will have to include
the Script within your EXEC call to execute it on the same execution
context.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"Ray Laubert" <ray@.rsl-webhosting.com> wrote in message
news:58B901C8-4603-4B6E-B8AF-C6DBEDD50EFC@.microsoft.com...
>I am trying to write a TSQL Script for maintenance that willl read the
> databases from sysdatabases and change to each database inturn to perform
> some maintenance tasks.
> Problem is I can't get SQL to change to the next database.
> Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
> change the database.
> I know there has to be a way... any ideas?
> Has to be available in TSQL as this will become a Stored Procedure when
> finished.
>
> --
> Raymond Laubert
> MCSE, MCDBA, MCT
>
Subscribe to:
Posts (Atom)