I need to change the email address that appears as the FROM email on reports
that are being delivered via RS2000. I have change the REPLY email and that
is working correctly but I can't seem to find where the FROM email is stored.
Thanks...It is configurable in the rsreportserver.config file on the report
server machine; edit that file and look for the <FROM> tag under the
<RSEmailDPConfiguration> section
Matt A
Steve wrote:
> I need to change the email address that appears as the FROM email on reports
> that are being delivered via RS2000. I have change the REPLY email and that
> is working correctly but I can't seem to find where the FROM email is stored.
> Thanks...|||That did it! Thanks Matt!!!
"Matt" wrote:
> It is configurable in the rsreportserver.config file on the report
> server machine; edit that file and look for the <FROM> tag under the
> <RSEmailDPConfiguration> section
> Matt A
>
> Steve wrote:
> > I need to change the email address that appears as the FROM email on reports
> > that are being delivered via RS2000. I have change the REPLY email and that
> > is working correctly but I can't seem to find where the FROM email is stored.
> > Thanks...
>|||what if I want to have a diffrent from address for each send?
same as you bind the To and the CC? can it be done?
"Steve" wrote:
> That did it! Thanks Matt!!!
> "Matt" wrote:
> > It is configurable in the rsreportserver.config file on the report
> > server machine; edit that file and look for the <FROM> tag under the
> > <RSEmailDPConfiguration> section
> >
> > Matt A
> >
> >
> > Steve wrote:
> > > I need to change the email address that appears as the FROM email on reports
> > > that are being delivered via RS2000. I have change the REPLY email and that
> > > is working correctly but I can't seem to find where the FROM email is stored.
> > > Thanks...
> >
> >
Showing posts with label via. Show all posts
Showing posts with label via. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Changing the command buffer size?
Is it possible to change the command buffer size??
I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer...
Thanks!!!Originally posted by rmcat
Is it possible to change the command buffer size??
I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer...
Thanks!!!
Are you sure it's your buffer that is causing the problem?
An Excel spread sheet can handle a max. of 65536 rows, maybe that is your limitation.|||Originally posted by kbk
Are you sure it's your buffer that is causing the problem?
An Excel spread sheet can handle a max. of 65536 rows, maybe that is your limitation.
When I run the stored proc with exec output turned on, I get the following error...
"Query hints exceed maximum command buffer size of 1023 bytes (3952 bytes input)."|||What do you put for the "query hints"? INDEX=xxxxx ?|||You're playing with dynamic sql...right?|||Originally posted by rdjabarov
What do you put for the "query hints"? INDEX=xxxxx ?
Ok, I don't exactly know what "query hints" in the error message is referring to, but the command I'm trying to execute is...
bcp "select...." queryout <filename> <sql user id> <sql password> <server> <file format>
The select statement is 3953 characters long. This commmand has worked for other (shorter) selects, so I have to assume the problem here is the length of my query. Unfortunately, I can't make it any shorter, so I'm hoping to make the command buffer bigger...|||Originally posted by Brett Kaiser
You're playing with dynamic sql...right?
Yes, it's dynamic. Sections of the where clause are dependant on data passed in from a web app that calls the stored proc.|||I think you nailed it, Brett! He just finished struggling with "Unclosed quotes"!|||Convert your SELECT into a stored procedure, and you're home free!|||Originally posted by rdjabarov
Convert your SELECT into a stored procedure, and you're home free!
Success!!!!
You guys rock!
Thank you!!!
I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer...
Thanks!!!Originally posted by rmcat
Is it possible to change the command buffer size??
I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer...
Thanks!!!
Are you sure it's your buffer that is causing the problem?
An Excel spread sheet can handle a max. of 65536 rows, maybe that is your limitation.|||Originally posted by kbk
Are you sure it's your buffer that is causing the problem?
An Excel spread sheet can handle a max. of 65536 rows, maybe that is your limitation.
When I run the stored proc with exec output turned on, I get the following error...
"Query hints exceed maximum command buffer size of 1023 bytes (3952 bytes input)."|||What do you put for the "query hints"? INDEX=xxxxx ?|||You're playing with dynamic sql...right?|||Originally posted by rdjabarov
What do you put for the "query hints"? INDEX=xxxxx ?
Ok, I don't exactly know what "query hints" in the error message is referring to, but the command I'm trying to execute is...
bcp "select...." queryout <filename> <sql user id> <sql password> <server> <file format>
The select statement is 3953 characters long. This commmand has worked for other (shorter) selects, so I have to assume the problem here is the length of my query. Unfortunately, I can't make it any shorter, so I'm hoping to make the command buffer bigger...|||Originally posted by Brett Kaiser
You're playing with dynamic sql...right?
Yes, it's dynamic. Sections of the where clause are dependant on data passed in from a web app that calls the stored proc.|||I think you nailed it, Brett! He just finished struggling with "Unclosed quotes"!|||Convert your SELECT into a stored procedure, and you're home free!|||Originally posted by rdjabarov
Convert your SELECT into a stored procedure, and you're home free!
Success!!!!
You guys rock!
Thank you!!!
Tuesday, 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!
Monday, March 19, 2012
Changing SQL server IP in Excel file?
An Excel file uses an SQL Server table to which it connects using MS
Query via ODBC. with the data being refreshed when needed with
Data/Refresh Data. The SQL server is moved to a different IP address
(say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
2000 can no longer find the table (obviously). How can one correct the
file to use the new SQLS address?
I have changed the SQLS entry in the ODBC Data Source Administrator --
this makes no difference, as the address is embedded in the XLS file,
and not picked up from the local machine's ODBC settings. If I choose
"Data/Edit Data" Excel tries to access the table and gives up with "SQL
Server does not exist". (And even with a valid SQL server address, I
couldn't find anywhere in the Query's options or SQL to change the SQL
server address).
I got round the problem with a binary file editor, replacing all 4
occurrences of "192.168.1.1" in the table with the new address (which
has the same number of characters).
But what is the proper way to do this, please?
Best wishes,
--
Michael SalemHi Michael,
I ran into a similar problem when I tried to use an excel sheet that was
developed on one server onto another. The only difference is that I am using
Excel 2003. When I tried to refresh the data, the system came up with an
error about invalid odbc connection. After acknowledging the error, it asks
to select another ODBC connection which I did and the refresh could then
complete properly. What exactly do you get when you initially refresh the
table?
Regards
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1c76f6f7aa5404cb989697@.msnews.microsoft.com...
> An Excel file uses an SQL Server table to which it connects using MS
> Query via ODBC. with the data being refreshed when needed with
> Data/Refresh Data. The SQL server is moved to a different IP address
> (say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
> 2000 can no longer find the table (obviously). How can one correct the
> file to use the new SQLS address?
> I have changed the SQLS entry in the ODBC Data Source Administrator --
> this makes no difference, as the address is embedded in the XLS file,
> and not picked up from the local machine's ODBC settings. If I choose
> "Data/Edit Data" Excel tries to access the table and gives up with "SQL
> Server does not exist". (And even with a valid SQL server address, I
> couldn't find anywhere in the Query's options or SQL to change the SQL
> server address).
> I got round the problem with a binary file editor, replacing all 4
> occurrences of "192.168.1.1" in the table with the new address (which
> has the same number of characters).
> But what is the proper way to do this, please?
> Best wishes,
> --
> Michael Salem|||I wrote:
Many thanks to imad koussa, who responded:
[vbcol=seagreen]
> I ran into a similar problem when I tried to use an excel sheet that was
> developed on one server onto another. The only difference is that I am usi
ng
> Excel 2003. When I tried to refresh the data, the system came up with an
> error about invalid odbc connection. After acknowledging the error, it ask
s
> to select another ODBC connection which I did and the refresh could then
> complete properly. What exactly do you get when you initially refresh the
> table?
After waiting for 64 seconds (a long time) I am asked to log in again. I
type in the correct address and the table is refreshed in 6 seconds.
But nothing I have been able to do stores the correct address in the
.XLS file. As it is used frequently by many users, I need to correct the
file, rather than wait a full minute and type in a numeric address.
This is not a major problem, but I would have expected to find a better
way to store the corrected information than editing the .XLS file with a
binary editor.
Best wishes,
--
Michael Salem
Query via ODBC. with the data being refreshed when needed with
Data/Refresh Data. The SQL server is moved to a different IP address
(say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
2000 can no longer find the table (obviously). How can one correct the
file to use the new SQLS address?
I have changed the SQLS entry in the ODBC Data Source Administrator --
this makes no difference, as the address is embedded in the XLS file,
and not picked up from the local machine's ODBC settings. If I choose
"Data/Edit Data" Excel tries to access the table and gives up with "SQL
Server does not exist". (And even with a valid SQL server address, I
couldn't find anywhere in the Query's options or SQL to change the SQL
server address).
I got round the problem with a binary file editor, replacing all 4
occurrences of "192.168.1.1" in the table with the new address (which
has the same number of characters).
But what is the proper way to do this, please?
Best wishes,
--
Michael SalemHi Michael,
I ran into a similar problem when I tried to use an excel sheet that was
developed on one server onto another. The only difference is that I am using
Excel 2003. When I tried to refresh the data, the system came up with an
error about invalid odbc connection. After acknowledging the error, it asks
to select another ODBC connection which I did and the refresh could then
complete properly. What exactly do you get when you initially refresh the
table?
Regards
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1c76f6f7aa5404cb989697@.msnews.microsoft.com...
> An Excel file uses an SQL Server table to which it connects using MS
> Query via ODBC. with the data being refreshed when needed with
> Data/Refresh Data. The SQL server is moved to a different IP address
> (say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
> 2000 can no longer find the table (obviously). How can one correct the
> file to use the new SQLS address?
> I have changed the SQLS entry in the ODBC Data Source Administrator --
> this makes no difference, as the address is embedded in the XLS file,
> and not picked up from the local machine's ODBC settings. If I choose
> "Data/Edit Data" Excel tries to access the table and gives up with "SQL
> Server does not exist". (And even with a valid SQL server address, I
> couldn't find anywhere in the Query's options or SQL to change the SQL
> server address).
> I got round the problem with a binary file editor, replacing all 4
> occurrences of "192.168.1.1" in the table with the new address (which
> has the same number of characters).
> But what is the proper way to do this, please?
> Best wishes,
> --
> Michael Salem|||I wrote:
Many thanks to imad koussa, who responded:
[vbcol=seagreen]
> I ran into a similar problem when I tried to use an excel sheet that was
> developed on one server onto another. The only difference is that I am usi
ng
> Excel 2003. When I tried to refresh the data, the system came up with an
> error about invalid odbc connection. After acknowledging the error, it ask
s
> to select another ODBC connection which I did and the refresh could then
> complete properly. What exactly do you get when you initially refresh the
> table?
After waiting for 64 seconds (a long time) I am asked to log in again. I
type in the correct address and the table is refreshed in 6 seconds.
But nothing I have been able to do stores the correct address in the
.XLS file. As it is used frequently by many users, I need to correct the
file, rather than wait a full minute and type in a numeric address.
This is not a major problem, but I would have expected to find a better
way to store the corrected information than editing the .XLS file with a
binary editor.
Best wishes,
--
Michael Salem
Changing SQL server IP in Excel file?
An Excel file uses an SQL Server table to which it connects using MS
Query via ODBC. with the data being refreshed when needed with
Data/Refresh Data. The SQL server is moved to a different IP address
(say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
2000 can no longer find the table (obviously). How can one correct the
file to use the new SQLS address?
I have changed the SQLS entry in the ODBC Data Source Administrator --
this makes no difference, as the address is embedded in the XLS file,
and not picked up from the local machine's ODBC settings. If I choose
"Data/Edit Data" Excel tries to access the table and gives up with "SQL
Server does not exist". (And even with a valid SQL server address, I
couldn't find anywhere in the Query's options or SQL to change the SQL
server address).
I got round the problem with a binary file editor, replacing all 4
occurrences of "192.168.1.1" in the table with the new address (which
has the same number of characters).
But what is the proper way to do this, please?
Best wishes,
Michael Salem
Hi Michael,
I ran into a similar problem when I tried to use an excel sheet that was
developed on one server onto another. The only difference is that I am using
Excel 2003. When I tried to refresh the data, the system came up with an
error about invalid odbc connection. After acknowledging the error, it asks
to select another ODBC connection which I did and the refresh could then
complete properly. What exactly do you get when you initially refresh the
table?
Regards
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1c76f6f7aa5404cb989697@.msnews.microsoft.c om...
> An Excel file uses an SQL Server table to which it connects using MS
> Query via ODBC. with the data being refreshed when needed with
> Data/Refresh Data. The SQL server is moved to a different IP address
> (say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
> 2000 can no longer find the table (obviously). How can one correct the
> file to use the new SQLS address?
> I have changed the SQLS entry in the ODBC Data Source Administrator --
> this makes no difference, as the address is embedded in the XLS file,
> and not picked up from the local machine's ODBC settings. If I choose
> "Data/Edit Data" Excel tries to access the table and gives up with "SQL
> Server does not exist". (And even with a valid SQL server address, I
> couldn't find anywhere in the Query's options or SQL to change the SQL
> server address).
> I got round the problem with a binary file editor, replacing all 4
> occurrences of "192.168.1.1" in the table with the new address (which
> has the same number of characters).
> But what is the proper way to do this, please?
> Best wishes,
> --
> Michael Salem
|||I wrote:
[vbcol=seagreen]
Many thanks to imad koussa, who responded:
> I ran into a similar problem when I tried to use an excel sheet that was
> developed on one server onto another. The only difference is that I am using
> Excel 2003. When I tried to refresh the data, the system came up with an
> error about invalid odbc connection. After acknowledging the error, it asks
> to select another ODBC connection which I did and the refresh could then
> complete properly. What exactly do you get when you initially refresh the
> table?
After waiting for 64 seconds (a long time) I am asked to log in again. I
type in the correct address and the table is refreshed in 6 seconds.
But nothing I have been able to do stores the correct address in the
..XLS file. As it is used frequently by many users, I need to correct the
file, rather than wait a full minute and type in a numeric address.
This is not a major problem, but I would have expected to find a better
way to store the corrected information than editing the .XLS file with a
binary editor.
Best wishes,
Michael Salem
Query via ODBC. with the data being refreshed when needed with
Data/Refresh Data. The SQL server is moved to a different IP address
(say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
2000 can no longer find the table (obviously). How can one correct the
file to use the new SQLS address?
I have changed the SQLS entry in the ODBC Data Source Administrator --
this makes no difference, as the address is embedded in the XLS file,
and not picked up from the local machine's ODBC settings. If I choose
"Data/Edit Data" Excel tries to access the table and gives up with "SQL
Server does not exist". (And even with a valid SQL server address, I
couldn't find anywhere in the Query's options or SQL to change the SQL
server address).
I got round the problem with a binary file editor, replacing all 4
occurrences of "192.168.1.1" in the table with the new address (which
has the same number of characters).
But what is the proper way to do this, please?
Best wishes,
Michael Salem
Hi Michael,
I ran into a similar problem when I tried to use an excel sheet that was
developed on one server onto another. The only difference is that I am using
Excel 2003. When I tried to refresh the data, the system came up with an
error about invalid odbc connection. After acknowledging the error, it asks
to select another ODBC connection which I did and the refresh could then
complete properly. What exactly do you get when you initially refresh the
table?
Regards
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1c76f6f7aa5404cb989697@.msnews.microsoft.c om...
> An Excel file uses an SQL Server table to which it connects using MS
> Query via ODBC. with the data being refreshed when needed with
> Data/Refresh Data. The SQL server is moved to a different IP address
> (say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
> 2000 can no longer find the table (obviously). How can one correct the
> file to use the new SQLS address?
> I have changed the SQLS entry in the ODBC Data Source Administrator --
> this makes no difference, as the address is embedded in the XLS file,
> and not picked up from the local machine's ODBC settings. If I choose
> "Data/Edit Data" Excel tries to access the table and gives up with "SQL
> Server does not exist". (And even with a valid SQL server address, I
> couldn't find anywhere in the Query's options or SQL to change the SQL
> server address).
> I got round the problem with a binary file editor, replacing all 4
> occurrences of "192.168.1.1" in the table with the new address (which
> has the same number of characters).
> But what is the proper way to do this, please?
> Best wishes,
> --
> Michael Salem
|||I wrote:
[vbcol=seagreen]
Many thanks to imad koussa, who responded:
> I ran into a similar problem when I tried to use an excel sheet that was
> developed on one server onto another. The only difference is that I am using
> Excel 2003. When I tried to refresh the data, the system came up with an
> error about invalid odbc connection. After acknowledging the error, it asks
> to select another ODBC connection which I did and the refresh could then
> complete properly. What exactly do you get when you initially refresh the
> table?
After waiting for 64 seconds (a long time) I am asked to log in again. I
type in the correct address and the table is refreshed in 6 seconds.
But nothing I have been able to do stores the correct address in the
..XLS file. As it is used frequently by many users, I need to correct the
file, rather than wait a full minute and type in a numeric address.
This is not a major problem, but I would have expected to find a better
way to store the corrected information than editing the .XLS file with a
binary editor.
Best wishes,
Michael Salem
Sunday, March 11, 2012
changing servers dynamically via TSQL
I know you can easily change databases, as in Use "dbname". Can one
also easily change database servers without having to manually select
the server name from the dropdown?
Thanks a million!
Not at the TSQL level. TSQL is executed by the database server, so you are already connected to the
database server when your TSQL code is executed. So this would have to be done by the client app.
SQL Server 2005 has a successor to OSQL.EXE named SQLCMD.EXE, and I believe that this tool has some
such functionality.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegr oups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>
|||no can do with tsql. if you use sql2k5 sqlcmd, you can use :Connect to
switch server connection.
:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P
password]] ,
Connects to an instance of SQL Server. Also closes the current connection.
-oj
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegr oups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>
|||Thanks all for the replies. Not what I wanted to hear, but truth leads
to wisdom, or so they say. :-)
Thanks again.
also easily change database servers without having to manually select
the server name from the dropdown?
Thanks a million!
Not at the TSQL level. TSQL is executed by the database server, so you are already connected to the
database server when your TSQL code is executed. So this would have to be done by the client app.
SQL Server 2005 has a successor to OSQL.EXE named SQLCMD.EXE, and I believe that this tool has some
such functionality.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegr oups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>
|||no can do with tsql. if you use sql2k5 sqlcmd, you can use :Connect to
switch server connection.
:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P
password]] ,
Connects to an instance of SQL Server. Also closes the current connection.
-oj
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegr oups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>
|||Thanks all for the replies. Not what I wanted to hear, but truth leads
to wisdom, or so they say. :-)
Thanks again.
changing servers dynamically via TSQL
I know you can easily change databases, as in Use "dbname". Can one
also easily change database servers without having to manually select
the server name from the dropdown?
Thanks a million!Not at the TSQL level. TSQL is executed by the database server, so you are already connected to the
database server when your TSQL code is executed. So this would have to be done by the client app.
SQL Server 2005 has a successor to OSQL.EXE named SQLCMD.EXE, and I believe that this tool has some
such functionality.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||no can do with tsql. if you use sql2k5 sqlcmd, you can use :Connect to
switch server connection.
:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P
password]] ,
Connects to an instance of SQL Server. Also closes the current connection.
-oj
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||Thanks all for the replies. Not what I wanted to hear, but truth leads
to wisdom, or so they say. :-)
Thanks again.
also easily change database servers without having to manually select
the server name from the dropdown?
Thanks a million!Not at the TSQL level. TSQL is executed by the database server, so you are already connected to the
database server when your TSQL code is executed. So this would have to be done by the client app.
SQL Server 2005 has a successor to OSQL.EXE named SQLCMD.EXE, and I believe that this tool has some
such functionality.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||no can do with tsql. if you use sql2k5 sqlcmd, you can use :Connect to
switch server connection.
:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P
password]] ,
Connects to an instance of SQL Server. Also closes the current connection.
-oj
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||Thanks all for the replies. Not what I wanted to hear, but truth leads
to wisdom, or so they say. :-)
Thanks again.
changing servers dynamically via TSQL
I know you can easily change databases, as in Use "dbname". Can one
also easily change database servers without having to manually select
the server name from the dropdown?
Thanks a million!Not at the TSQL level. TSQL is executed by the database server, so you are a
lready connected to the
database server when your TSQL code is executed. So this would have to be do
ne by the client app.
SQL Server 2005 has a successor to OSQL.EXE named SQLCMD.EXE, and I believe
that this tool has some
such functionality.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||no can do with tsql. if you use sql2k5 sqlcmd, you can use :Connect to
switch server connection.
:Connect server_name[\instance_name] [-l timeout] [-U user_name
[-P
password]] ,
Connects to an instance of SQL Server. Also closes the current connection.
-oj
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||Thanks all for the replies. Not what I wanted to hear, but truth leads
to wisdom, or so they say. :-)
Thanks again.
also easily change database servers without having to manually select
the server name from the dropdown?
Thanks a million!Not at the TSQL level. TSQL is executed by the database server, so you are a
lready connected to the
database server when your TSQL code is executed. So this would have to be do
ne by the client app.
SQL Server 2005 has a successor to OSQL.EXE named SQLCMD.EXE, and I believe
that this tool has some
such functionality.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||no can do with tsql. if you use sql2k5 sqlcmd, you can use :Connect to
switch server connection.
:Connect server_name[\instance_name] [-l timeout] [-U user_name
[-P
password]] ,
Connects to an instance of SQL Server. Also closes the current connection.
-oj
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||Thanks all for the replies. Not what I wanted to hear, but truth leads
to wisdom, or so they say. :-)
Thanks again.
changing schema owner through SMO
I'm getting the can't drop user error. Is there a way to change the schema owner via smo? I've tried the following code to change the schema owner back to a different user. I don't get an error or exception but the schema owner doesn't change.
Database database = new Server("my server").Databases["my Database"];
database.Schemas["db_owner"].Owner = "db_owner";
Never mind, the following worked.
Database database = new Server("my server").Databases["my Database"];
database.Schemas["db_owner"].Owner = "db_owner";
database.Schemas["db_owner"].Alter();
|||John, giving that your question is SMO related, I split it from the thread you posted it in and I moved it to the SMO forum.
Thanks
Laurentiu
Changing Scale Minimum value on a Graph
How can we change in code the Scale Minimum value via code, depending
on the data in a dataset?
What I would like to beable to do is find the min. value in the dataset
and then set Scale Minimum valueBryan,
I had contacted Dundas support via email on this subject on 3/29 and they
said that it was not possible via the RS version and noted they don't provide
support for RS and directed me to Microsoft's forum websites.
"Bryan Avery" wrote:
> How can we change in code the Scale Minimum value via code, depending
> on the data in a dataset?
> What I would like to beable to do is find the min. value in the dataset
> and then set Scale Minimum value
>|||RS 2000: If you leave the Min value empty, the chart control will
"auto-scale" the minimum based on the actual data values.
RS 2005: you will be able to specify an expression for the min/max values.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"yrmeyer" <yrmeyer@.discussions.microsoft.com> wrote in message
news:6DDA957A-3D62-4CC9-87F9-7374DF9AA1C1@.microsoft.com...
> Bryan,
> I had contacted Dundas support via email on this subject on 3/29 and they
> said that it was not possible via the RS version and noted they don't
> provide
> support for RS and directed me to Microsoft's forum websites.
> "Bryan Avery" wrote:
>> How can we change in code the Scale Minimum value via code, depending
>> on the data in a dataset?
>> What I would like to beable to do is find the min. value in the dataset
>> and then set Scale Minimum value
>>
on the data in a dataset?
What I would like to beable to do is find the min. value in the dataset
and then set Scale Minimum valueBryan,
I had contacted Dundas support via email on this subject on 3/29 and they
said that it was not possible via the RS version and noted they don't provide
support for RS and directed me to Microsoft's forum websites.
"Bryan Avery" wrote:
> How can we change in code the Scale Minimum value via code, depending
> on the data in a dataset?
> What I would like to beable to do is find the min. value in the dataset
> and then set Scale Minimum value
>|||RS 2000: If you leave the Min value empty, the chart control will
"auto-scale" the minimum based on the actual data values.
RS 2005: you will be able to specify an expression for the min/max values.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"yrmeyer" <yrmeyer@.discussions.microsoft.com> wrote in message
news:6DDA957A-3D62-4CC9-87F9-7374DF9AA1C1@.microsoft.com...
> Bryan,
> I had contacted Dundas support via email on this subject on 3/29 and they
> said that it was not possible via the RS version and noted they don't
> provide
> support for RS and directed me to Microsoft's forum websites.
> "Bryan Avery" wrote:
>> How can we change in code the Scale Minimum value via code, depending
>> on the data in a dataset?
>> What I would like to beable to do is find the min. value in the dataset
>> and then set Scale Minimum value
>>
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 Field Lenght via query analyzer
Hi
My DB is on the web so but I want change Field Lenght of one of my Fileds on my DB
its 70 rights now I want change it to 120 its a nvarchar
how can I do it , I am using a ASP page
ThanksALTER TABLE tablename ALTER COLUMN columnname NVARCHAR(120)
You can just pass this statement to the database.
You should check out Books Online. it's free documentation that comes with SQL server.
My DB is on the web so but I want change Field Lenght of one of my Fileds on my DB
its 70 rights now I want change it to 120 its a nvarchar
how can I do it , I am using a ASP page
ThanksALTER TABLE tablename ALTER COLUMN columnname NVARCHAR(120)
You can just pass this statement to the database.
You should check out Books Online. it's free documentation that comes with SQL server.
Sunday, February 12, 2012
Changing DatasetName and Datasource programatically
Hello,
Does anyone know if it's possible to programatically change the
datasetName and/or dataSource via either the reportViewer.dll or
through report parameters?
My situation is that I need to be able to switch the database and query
statement from an active database to an archive database, based on a
user selection of whether or not they want to report off the
active/archived database. The report itself and the fields used in it
are identically named in both the archive and active database.
Thus, I'm trying to avoid having to create a duplicate report where the
only difference is the datasource/dataset. Is this possible?
Thank you for any help!You could write a custom data processing extension (DPE). There's probably
an easier way but that would definitely work... althought it isn't
straightfoward or simple.
"kevin.haynie@.gmail.com" wrote:
> Hello,
> Does anyone know if it's possible to programatically change the
> datasetName and/or dataSource via either the reportViewer.dll or
> through report parameters?
> My situation is that I need to be able to switch the database and query
> statement from an active database to an archive database, based on a
> user selection of whether or not they want to report off the
> active/archived database. The report itself and the fields used in it
> are identically named in both the archive and active database.
> Thus, I'm trying to avoid having to create a duplicate report where the
> only difference is the datasource/dataset. Is this possible?
> Thank you for any help!
>|||Hi Kevin,
The easiest way is through report parameters, as you said. All you have
to do is build conditional code around the parameters, and make it
select which DB the user wants to see a report of.
Lance M.
kevin.haynie@.gmail.com wrote:
> Hello,
> Does anyone know if it's possible to programatically change the
> datasetName and/or dataSource via either the reportViewer.dll or
> through report parameters?
> My situation is that I need to be able to switch the database and query
> statement from an active database to an archive database, based on a
> user selection of whether or not they want to report off the
> active/archived database. The report itself and the fields used in it
> are identically named in both the archive and active database.
> Thus, I'm trying to avoid having to create a duplicate report where the
> only difference is the datasource/dataset. Is this possible?
> Thank you for any help!
Does anyone know if it's possible to programatically change the
datasetName and/or dataSource via either the reportViewer.dll or
through report parameters?
My situation is that I need to be able to switch the database and query
statement from an active database to an archive database, based on a
user selection of whether or not they want to report off the
active/archived database. The report itself and the fields used in it
are identically named in both the archive and active database.
Thus, I'm trying to avoid having to create a duplicate report where the
only difference is the datasource/dataset. Is this possible?
Thank you for any help!You could write a custom data processing extension (DPE). There's probably
an easier way but that would definitely work... althought it isn't
straightfoward or simple.
"kevin.haynie@.gmail.com" wrote:
> Hello,
> Does anyone know if it's possible to programatically change the
> datasetName and/or dataSource via either the reportViewer.dll or
> through report parameters?
> My situation is that I need to be able to switch the database and query
> statement from an active database to an archive database, based on a
> user selection of whether or not they want to report off the
> active/archived database. The report itself and the fields used in it
> are identically named in both the archive and active database.
> Thus, I'm trying to avoid having to create a duplicate report where the
> only difference is the datasource/dataset. Is this possible?
> Thank you for any help!
>|||Hi Kevin,
The easiest way is through report parameters, as you said. All you have
to do is build conditional code around the parameters, and make it
select which DB the user wants to see a report of.
Lance M.
kevin.haynie@.gmail.com wrote:
> Hello,
> Does anyone know if it's possible to programatically change the
> datasetName and/or dataSource via either the reportViewer.dll or
> through report parameters?
> My situation is that I need to be able to switch the database and query
> statement from an active database to an archive database, based on a
> user selection of whether or not they want to report off the
> active/archived database. The report itself and the fields used in it
> are identically named in both the archive and active database.
> Thus, I'm trying to avoid having to create a duplicate report where the
> only difference is the datasource/dataset. Is this possible?
> Thank you for any help!
Subscribe to:
Posts (Atom)