Get Authentication Error in upgrade SQL 2005 named instance to SP1 TCP

I got the following error using either NT or SQL Login ('sa') that are sysadmin in my SQL 2005 instance:

Your Account information could not be verified for one or more instances. Ensure that you can connect to all the selected instances using the account information provided. To process further deselect the instances to which connectivity cannot be established.

However, I would login to SQL 2005 named instance, either NT or SQL login 'sa' without any problems to do anything I want because both are sysadmin.

<PS> Note that my default SQL instance in my local server is SQL 2000 and SQL 2005 named instance.

Richard

Hi Richard, I just tried this on my machine and didn't hit any issues. Can you send a little more info about the state of your machine? Are you running SQL 2000 SP4? What SKU is SQL 2000 and what SKU is 2005? Are they both ENT? And how are you connecting to the 2005 named instance? I was able to install SP1 using Windows Authentication. Can you submit your install logs so I can try to help debug?

Here's what I did:
1) Install SQL 2000 ENT SKU as local system, setting SA password - default instance
2) Apply SQL 2000 SP4
3) Install SQL 2005 ENT SKU setting SQL to run under domain account - named instance
4) Install SQL 2005 SP1

Does that look similar to your repro steps?

Thanks,
Samuel Lester (MSFT)

|||

What I hae done is 1), 3), and get the error for 4). Attached is the log file for your information:

4 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:40.464 splevel = 0,1
04/03/2006 15:54:40.504 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:40.574 upgradecode = {1B117BA7-5BC1-419E-820E-7D4F3F412C7B}
04/03/2006 15:54:40.644 version = 9
04/03/2006 15:54:40.694
04/03/2006 15:54:40.714 File Group Details: MSI
04/03/2006 15:54:40.734 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:40.764 File Details: msxml6.msi
04/03/2006 15:54:40.784
04/03/2006 15:54:40.824 Instance Details: MSXML 6.0 Parser
04/03/2006 15:54:40.854 productcode = {AEB9948B-4FF2-47C9-990E-47014492A0FE}
04/03/2006 15:54:40.884
04/03/2006 15:54:40.904 Product Details:
04/03/2006 15:54:40.924 INF File Name: \\2003SERVER\d$\9a954d1afa2c57beebe098\HotFixMsxml6_x64.inf
04/03/2006 15:54:40.944 baselinebuild = 1399
04/03/2006 15:54:40.994 build = 2040
04/03/2006 15:54:41.015 description = MSXML 6.0 Parser (64-bit)
04/03/2006 15:54:41.045 details = Service Pack for Microsoft XML 6.0 Parser.
04/03/2006 15:54:41.075 kbarticle = KB913090
04/03/2006 15:54:41.115 kbarticlehyperlink = http://support.microsoft.com/?kbid=913090
04/03/2006 15:54:41.155 lcid = 1033
04/03/2006 15:54:41.195 legalproductname = MSXML 6.0 Parser (64-bit)
04/03/2006 15:54:41.215 machinetype = x64
04/03/2006 15:54:41.305 package = HotFixMsxml6_x64
04/03/2006 15:54:41.325 packagetype = Hotfix
04/03/2006 15:54:41.355 productname = Redist9
04/03/2006 15:54:41.405 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:41.435 splevel = 0,1
04/03/2006 15:54:41.455 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:41.475 upgradecode = {5BBED1F8-E6F3-4A02-BC97-26D35BE200CA}
04/03/2006 15:54:41.595 version = 9
04/03/2006 15:54:41.695
04/03/2006 15:54:41.776 File Group Details: MSI
04/03/2006 15:54:41.856 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:41.946 File Details: msxml6_x64.msi
04/03/2006 15:54:42.196
04/03/2006 15:54:42.296 Product Details:
04/03/2006 15:54:42.396 INF File Name: \\2003SERVER\d$\9a954d1afa2c57beebe098\HotFixSqlxml4.inf
04/03/2006 15:54:42.507 baselinebuild = 1399
04/03/2006 15:54:42.617 build = 2040
04/03/2006 15:54:42.687 description = SQLXML4
04/03/2006 15:54:42.757 details = Service Pack for Microsoft SQLXML 4.0.
04/03/2006 15:54:42.837 kbarticle = KB913090
04/03/2006 15:54:42.937 kbarticlehyperlink = http://support.microsoft.com/?kbid=913090
04/03/2006 15:54:43.037 lcid = 1033
04/03/2006 15:54:43.138 legalproductname = SQLXML4
04/03/2006 15:54:43.238 machinetype = x86
04/03/2006 15:54:43.368 package = HotFixSqlxml4
04/03/2006 15:54:43.398 packagetype = Hotfix
04/03/2006 15:54:43.438 productcode = {8C62A94B-4AB6-485F-A111-93056684D340}
04/03/2006 15:54:43.458 productname = Redist9
04/03/2006 15:54:43.488 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:43.578 splevel = 0,1
04/03/2006 15:54:43.638 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:43.678 upgradecode = {D9CA3D82-6F1B-41A7-8141-B90ACA8F865B}
04/03/2006 15:54:43.708 version = 9
04/03/2006 15:54:43.748
04/03/2006 15:54:43.768 File Group Details: MSI
04/03/2006 15:54:43.809 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:43.829 File Details: sqlxml4.msi
04/03/2006 15:54:43.869
04/03/2006 15:54:43.899 Instance Details: SQLXML4
04/03/2006 15:54:43.919 productcode = {8C62A94B-4AB6-485F-A111-93056684D340}
04/03/2006 15:54:43.969
04/03/2006 15:54:43.999 Product Details:
04/03/2006 15:54:44.019 INF File Name: \\2003SERVER\d$\9a954d1afa2c57beebe098\HotFixSqlxml4_x64.inf
04/03/2006 15:54:44.069 baselinebuild = 1399
04/03/2006 15:54:44.099 build = 2040
04/03/2006 15:54:44.129 description = SQLXML4 (64-bit)
04/03/2006 15:54:44.169 details = Service Pack for Microsoft SQLXML 4.0.
04/03/2006 15:54:44.199 kbarticle = KB913090
04/03/2006 15:54:44.229 kbarticlehyperlink = http://support.microsoft.com/?kbid=913090
04/03/2006 15:54:44.269 lcid = 1033
04/03/2006 15:54:44.299 legalproductname = SQLXML4 (64-bit)
04/03/2006 15:54:44.329 machinetype = x64
04/03/2006 15:54:44.359 package = HotFixSqlxml4_x64
04/03/2006 15:54:44.399 packagetype = Hotfix
04/03/2006 15:54:44.429 productname = Redist9
04/03/2006 15:54:44.459 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:44.510 splevel = 0,1
04/03/2006 15:54:44.600 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:44.650 upgradecode = {F457D8E6-7686-437D-9B17-E21D45CCABD8}
04/03/2006 15:54:44.680 version = 9
04/03/2006 15:54:44.720
04/03/2006 15:54:44.750 File Group Details: MSI
04/03/2006 15:54:44.780 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:44.820 File Details: sqlxml4_x64.msi
04/03/2006 15:54:44.850
04/03/2006 15:54:44.880 Product Details:
04/03/2006 15:54:44.920 INF File Name: \\2003SERVER\d$\9a954d1afa2c57beebe098\HotFixSQLServer2005_BC.inf
04/03/2006 15:54:44.950 baselinebuild = 1399
04/03/2006 15:54:44.980 build = 2040
04/03/2006 15:54:45.020 description = Backward Compatibility
04/03/2006 15:54:45.050 details = Service Pack for the Backward Compatibility components, including Data Transformation Services Runtime and SQL-DMO.
04/03/2006 15:54:45.080 kbarticle = KB913090
04/03/2006 15:54:45.120 kbarticlehyperlink = http://support.microsoft.com/?kbid=913090
04/03/2006 15:54:45.160 lcid = 1033
04/03/2006 15:54:45.191 legalproductname = Microsoft SQL Server 2005 Backward Compatibility
04/03/2006 15:54:45.231 machinetype = x86
04/03/2006 15:54:45.261 package = HotFixSQLServer2005_BC
04/03/2006 15:54:45.291 packagetype = Hotfix
04/03/2006 15:54:45.321 productcode = {96327C3C-96BE-4C7A-A6F7-A71635E5949A}
04/03/2006 15:54:45.361 productname = Redist9
04/03/2006 15:54:45.391 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:45.421 splevel = 0,1
04/03/2006 15:54:45.461 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:45.491 upgradecode = {1E70C6C9-E1B7-4A74-BC8C-8EB5D010CEC9}
04/03/2006 15:54:45.521 version = 9
04/03/2006 15:54:45.561
04/03/2006 15:54:45.591 File Group Details: MSI
04/03/2006 15:54:45.621 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:45.661 File Details: SQLServer2005_BC.msi
04/03/2006 15:54:45.691
04/03/2006 15:54:45.721 Instance Details: Backward Compatibility
04/03/2006 15:54:45.761 productcode = {96327C3C-96BE-4C7A-A6F7-A71635E5949A}
04/03/2006 15:54:45.791
04/03/2006 15:54:45.821 Product Details:
04/03/2006 15:54:45.851 INF File Name: \\2003SERVER\d$\9a954d1afa2c57beebe098\HotFixSQLServer2005_BC_x64.inf
04/03/2006 15:54:45.892 baselinebuild = 1399
04/03/2006 15:54:45.922 build = 2040
04/03/2006 15:54:45.952 description = Backward Compatibility (64-bit)
04/03/2006 15:54:45.992 details = Service Pack for the Backward Compatibility components, including Data Transformation Services Runtime and SQL-DMO.
04/03/2006 15:54:46.022 kbarticle = KB913090
04/03/2006 15:54:46.052 kbarticlehyperlink = http://support.microsoft.com/?kbid=913090
04/03/2006 15:54:46.092 lcid = 1033
04/03/2006 15:54:46.122 legalproductname = Microsoft SQL Server 2005 Backward Compatibility (64-bit)
04/03/2006 15:54:46.152 machinetype = x64
04/03/2006 15:54:46.192 package = HotFixSQLServer2005_BC_x64
04/03/2006 15:54:46.222 packagetype = Hotfix
04/03/2006 15:54:46.322 productname = Redist9
04/03/2006 15:54:46.362 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:46.402 splevel = 0,1
04/03/2006 15:54:46.432 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:46.462 upgradecode = {7B6BF434-3C72-4DB3-8049-FEF31AEAFF9A}
04/03/2006 15:54:46.492 version = 9
04/03/2006 15:54:46.532
04/03/2006 15:54:46.562 File Group Details: MSI
04/03/2006 15:54:46.593 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:46.633 File Details: SQLServer2005_BC_x64.msi
04/03/2006 15:54:46.663
04/03/2006 15:54:46.693 Product Details:
04/03/2006 15:54:46.733 INF File Name: \\2003SERVER\d$\9a954d1afa2c57beebe098\HotFixSqlWriter.inf
04/03/2006 15:54:46.763 baselinebuild = 1399
04/03/2006 15:54:46.793 build = 2040
04/03/2006 15:54:46.833 description = Microsoft SQL Server VSS Writer
04/03/2006 15:54:46.863 details = Service Pack for Microsoft SQL Server VSS Writer.
04/03/2006 15:54:46.893 kbarticle = KB913090
04/03/2006 15:54:46.933 kbarticlehyperlink = http://support.microsoft.com/?kbid=913090
04/03/2006 15:54:46.973 lcid = 1033
04/03/2006 15:54:47.003 legalproductname = Microsoft SQL Server VSS Writer
04/03/2006 15:54:47.053 machinetype = x86
04/03/2006 15:54:47.083 package = HotFixSqlWriter
04/03/2006 15:54:47.113 packagetype = Hotfix
04/03/2006 15:54:47.153 productcode = {1CBE3804-20DF-48DA-B048-895C206E80A5}
04/03/2006 15:54:47.183 productname = Redist9
04/03/2006 15:54:47.213 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:47.253 splevel = 0,1
04/03/2006 15:54:47.284 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:47.314 upgradecode = {65D8E1DF-6201-4B53-A0F9-E654F8E80F97}
04/03/2006 15:54:47.344 version = 9
04/03/2006 15:54:47.384
04/03/2006 15:54:47.414 File Group Details: MSI
04/03/2006 15:54:47.444 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:47.484 File Details: SqlWriter.msi
04/03/2006 15:54:47.514
04/03/2006 15:54:47.544 Instance Details: Microsoft SQL Server VSS Writer
04/03/2006 15:54:47.584 productcode = {1CBE3804-20DF-48DA-B048-895C206E80A5}
04/03/2006 15:54:47.614
04/03/2006 15:54:47.644 Product Details:
04/03/2006 15:54:47.684 INF File Name: \\2003SERVER\d$\9a954d1afa2c57beebe098\HotFixSqlWriter_x64.inf
04/03/2006 15:54:47.714 baselinebuild = 1399
04/03/2006 15:54:47.744 build = 2040
04/03/2006 15:54:47.774 description = Microsoft SQL Server VSS Writer (64-bit)
04/03/2006 15:54:47.814 details = Service Pack for Microsoft SQL Server VSS Writer.
04/03/2006 15:54:47.844 kbarticle = KB913090
04/03/2006 15:54:47.874 kbarticlehyperlink = http://support.microsoft.com/?kbid=913090
04/03/2006 15:54:47.944 lcid = 1033
04/03/2006 15:54:47.995 legalproductname = Microsoft SQL Server VSS Writer (64-bit)
04/03/2006 15:54:48.045 machinetype = x64
04/03/2006 15:54:48.085 package = HotFixSqlWriter_x64
04/03/2006 15:54:48.155 packagetype = Hotfix
04/03/2006 15:54:48.225 productname = Redist9
04/03/2006 15:54:48.275 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:48.345 splevel = 0,1
04/03/2006 15:54:48.405 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:48.475 upgradecode = {E9031696-DD39-4C25-BAEB-425FF28279EA}
04/03/2006 15:54:48.625 version = 9
04/03/2006 15:54:48.716
04/03/2006 15:54:48.756 File Group Details: MSI
04/03/2006 15:54:48.786 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:48.816 File Details: SqlWriter_x64.msi
04/03/2006 15:54:48.856
04/03/2006 15:55:14.993 Authenticating user using Windows Authentication
04/03/2006 15:55:15.073 SQL Service MSSQL$YUKON was previously running, ready for authentication
04/03/2006 15:55:15.114 SQL Agent Service SQLAgent$YUKON was not previously running
04/03/2006 15:55:24.247 User authentication failed. Please check to see if the password provided is correct or check to see if the SQL Server Instance is set to Mixed Authentication
04/03/2006 15:55:33.009 Failed to receive sysadmin status for RS instance: YUKON
04/03/2006 15:55:58.786 Authenticating user using SAPWD
04/03/2006 15:55:58.836 SQL Service MSSQL$YUKON was previously running, ready for authentication
04/03/2006 15:55:58.866 SQL Agent Service SQLAgent$YUKON was not previously running
04/03/2006 15:56:07.509 User authentication failed. Please check to see if the password provided is correct or check to see if the SQL Server Instance is set to Mixed Authentication
04/03/2006 15:56:16.151 Failed to receive sysadmin status for RS instance: YUKON
04/03/2006 15:57:24.440 Hotfix package closed

|||I get the same problem. I have installed SQL 2000 SP4 and it did not fix it.

generic user versus named user

Good morning,
We are having a debate here about development. We in
programming would like to create a generic user account
for example TransDev and have the users use that to create
an ODBC connection to the database versus having it
reference a person. The network admin group says that's a
horrible thing to request. I didn't find such an instance
on your website which answered a lot of my other
questions. Do have a perspective from a security
standpoint where such a request would be a bad thing?
Thanks in advance.
Laura MayerThe advantage of individual accounts is that you can control security per
user. Otherwise, you have no way to restrict access because the
login/password is well known.
A best practice is to create roles and grant permissions only to roles. You
can then control security via user role membership. This works especially
well with Windows authentication since you can login to SQL Server with
individual user credentials without bothering with a separate account.
Hope this helps.
Dan Guzman
SQL Server MVP
"Laura" <anonymous@.discussions.microsoft.com> wrote in message
news:bbda01c4082c$49c97820$a401280a@.phx.gbl...
> Good morning,
> We are having a debate here about development. We in
> programming would like to create a generic user account
> for example TransDev and have the users use that to create
> an ODBC connection to the database versus having it
> reference a person. The network admin group says that's a
> horrible thing to request. I didn't find such an instance
> on your website which answered a lot of my other
> questions. Do have a perspective from a security
> standpoint where such a request would be a bad thing?
> Thanks in advance.
> Laura Mayersql

generic account vs. named account

Good morning,
We are having a debate here about development. We in
programming would like to create a generic user account
for example TransDev and have the users use that to create
an ODBC connection to the database versus having it
reference a person. The network admin group says that's a
horrible thing to request. I didn't find such an instance
on your website which answered a lot of my other
questions. Does anyone have a perspective from a security
standpoint where such a request would be a bad thing?
Thanks in advance.
Laura Mayer
Database Analyst
C&S Wholesale Grocers
802-257-6855first, if everyone is using the same id, it's much more difficult to
tell who screwed something up. also much easier for that login and
password to fall into the wrong hands.
since your network admins seem to hate your idea, force them to do some
work (if they run your authentication servers). have them create an
ntgroup and add all the users that need access to the db. then you give
the ntgroup the permissions it needs in the db. then when someone new
needs access to the db or someone leaves, the network admins will have
to go change the ntgroup and you won't have to do anything.
Laura wrote:

> Good morning,
> We are having a debate here about development. We in
> programming would like to create a generic user account
> for example TransDev and have the users use that to create
> an ODBC connection to the database versus having it
> reference a person. The network admin group says that's a
> horrible thing to request. I didn't find such an instance
> on your website which answered a lot of my other
> questions. Does anyone have a perspective from a security
> standpoint where such a request would be a bad thing?
> Thanks in advance.
> Laura Mayer
> Database Analyst
> C&S Wholesale Grocers
> 802-257-6855|||Thanks for that. What do you think about the ODBC
connection? Currently we are setting up the connection
using a persons name with the appropriate connections. I
would like to have a generic account. Could I have them
create that and put it in the group? Would that be an
appropriate middle ground?
Thanks
Laura
>--Original Message--
>first, if everyone is using the same id, it's much more
difficult to
>tell who screwed something up. also much easier for that
login and
>password to fall into the wrong hands.
>since your network admins seem to hate your idea, force
them to do some
>work (if they run your authentication servers). have
them create an
>ntgroup and add all the users that need access to the
db. then you give
>the ntgroup the permissions it needs in the db. then
when someone new
>needs access to the db or someone leaves, the network
admins will have
>to go change the ntgroup and you won't have to do
anything.
>
>Laura wrote:
>
create
a
instance
security
>.
>|||not really. you would put the ntusers into the ntgroup. then when
setting up the odbc, you'd choose windows authentication instead of
sqlserver authentication.
for example, you have users joe and john both members of domain1.
you create a group in domain1 called sqlgrp.
add domain1\joe and domain1\john to domain1\sqlgrp.
assign db permissions to domain1\sqlgrp.
set up odbc to use windows authentication.
that way there are no additional passwords to manage, only one set of db
permissions to manage, greater security with windows accounts versus
sqlserver accounts.
Laura wrote:
> Thanks for that. What do you think about the ODBC
> connection? Currently we are setting up the connection
> using a persons name with the appropriate connections. I
> would like to have a generic account. Could I have them
> create that and put it in the group? Would that be an
> appropriate middle ground?
> Thanks
> Laura
> difficult to
> login and
> them to do some
> them create an
> db. then you give
> when someone new
> admins will have
> anything.
> create
> a
> instance
> security|||Adding to the comments of 'ch', from your ODBC connection string you can use
the:
Trusted_Connection=yes;
option to force the authentication to be "trusted". Stay away from generic
access accounts they can not be monitored, creating a huge security hole
(IMO).
Steve
"Laura" <anonymous@.discussions.microsoft.com> wrote in message
news:bb1501c40834$75b42b80$a501280a@.phx.gbl...
> Thanks for that. What do you think about the ODBC
> connection? Currently we are setting up the connection
> using a persons name with the appropriate connections. I
> would like to have a generic account. Could I have them
> create that and put it in the group? Would that be an
> appropriate middle ground?
> Thanks
> Laura
> difficult to
> login and
> them to do some
> them create an
> db. then you give
> when someone new
> admins will have
> anything.
> create
> a
> instance
> security

Generating Sequence Number....

Hi,
I got stuck with a trivial issue. I have a table named T1 having column as Col1 (INT). I have another table named T2 having columns Col1(Int), COl2(Int). Following are the reacords in my table T1.

Col1
--
1
1
1
1
2
2
3
3
3
3
3
4
5
6
6
7

And I want to migrate the data from T1 to T2 where the Col1 data of T1 will get migrated to Col1 of T1 and after the migration is done the T2 should have the data like this.......

Col1 Col2
-- --
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 4
3 5
4 1
5 1
6 1
6 2
7 1

Thanks In Advance,
Rahul JhaI thought this would be a fun one to try - so here's a very poor attempt using an icky cursor... I don't think I have to explain to a DBA such as yourself why you shouldn't implement this method ;)

DECLARE @.col1 int
DECLARE @.col2 int
DECLARE @.id int
DECLARE @.i int
DECLARE @.x int
DECLARE @.myTable table (
id int identity(1,1)
, col1 int
, col2 int
)

SET NOCOUNT ON

INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(2)
INSERT INTO @.myTable(col1) VALUES(2)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(4)
INSERT INTO @.myTable(col1) VALUES(5)
INSERT INTO @.myTable(col1) VALUES(6)
INSERT INTO @.myTable(col1) VALUES(6)
INSERT INTO @.myTable(col1) VALUES(7)

SET @.i = 1
SET @.x = 1

DECLARE theIckyCursor cursor
FOR
SELECT id
, col1
FROM @.myTable
ORDER
BY col1 ASC

OPEN theIckyCursor

FETCH NEXT FROM theIckyCursor INTO @.id, @.col1

WHILE @.@.Fetch_Status <> -1 BEGIN
IF @.x <> @.col1 BEGIN
SET @.x = @.x + 1
SET @.i = 1
END

UPDATE @.myTable
SET col2 = @.i
WHERE id = @.id

SET @.i = @.i + 1
FETCH NEXT FROM theIckyCursor INTO @.id, @.col1
END

CLOSE theIckyCursor
DEALLOCATE theIckyCursor

SELECT col1
, col2
FROM @.myTable

SET NOCOUNT OFF

You know... If you provide us with *real* data, rather than just T1, T2 etc then a better answer could be provided.

(Basically, what you're trying to do sounds very wrong!)|||thnkx georgy. Even I was trying on the same line :-). But what I am looking forward is some process (without cursor) or just the query that can do the needfull. there is a huge data migration to be done. hence suggest me some thing that doesn't eat much of the system resource.|||a table with only one column where there are duplicate values cannot possibly have a primary key, so you cannot expect a good solution

export your data to excel, use a macro to apply the sequence numbers

otherwise, tell us your real situation, not this fantasy of only one column|||Ok, let's go back to the real world scenario.
Can you provide proper column headings etc and describe WHY you want to add a sequence number?
If you explain your justification for this design; perhaps we can suggest a better solution :)
EDIT: Sniped!|||Goergy I won't able able to give you the real world schema as it's highly confidential. Scenario is exactly teh same as i said. the only difference is that there are more columns than I said in both the tables.

Why I need this, is because of i am migrating the data from and de-normalised database to a normalised one.

Thanks,
Rahul Jha|||can you use an ORDER BY to get the rows into the necessary sequence for numbering them?|||select col1,row_number()over(partition by col1 order by col1)col2 into t2 from t1|||row_number?

Can you make it more clear?

Thanks,
Rahul Jha|||row_number() (http://www.databasejournal.com/features/mssql/article.php/3572301)|||pdreyer's solution actually works. I have learned something new today :)|||IVON, can you paste the code for the same. I am not getting the meaning of row_number(). How to use this function.

I'll be obliged if any one let me know on the query that pdreyer has written.

Thanks,
Rahul Jha|||I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000. And my DB is in SS 2000.

Thanks,
Rahul Jha|||please answer the question in post #7|||This one ought to leave a mark!SELECT 1 AS ick
INTO #junque
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 6
UNION ALL SELECT 7

SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
INTO #foo
FROM #junque

UPDATE #foo
SET bat = (SELECT Count(*)
FROM #foo AS baz
WHERE baz.ick = #foo.ick
AND baz.bar <= #foo.bar)

SELECT ick, bat
FROM #foo

DROP TABLE #foo
DROP TABLE #junque-PatP|||ORDER BY on COL1 can be done......... Let me know if i hv cleared ur doubt or not............|||have you seen pats post on page 1|||This one ought to leave a mark!ouch!

[applies mark remover]|||I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000.
True, true.
And my DB is in SS 2000.
We didn't know that.|||ORDER BY on COL1 can be doneexcellent

here is your solution:

select ( select count(*) + 1
from daTable
where Col1 < T.Col1 ) as rownum
, Col1
from daTable as T
order
by Col1

rownum Col1
1 1
1 1
1 1
1 1
5 2
5 2
7 3
7 3
7 3
7 3
7 3
12 4
13 5
14 6
14 6
16 7
here is pdreyer's ROW_NUMBER() solution for comparison --

select row_number() over
( partition by Col1
order by Col1 ) as rownum
, Col1
from daTable
order
by Col1

rownum Col1
1 1
2 1
3 1
4 1
1 2
2 2
1 3
2 3
3 3
4 3
5 3
1 4
1 5
1 6
2 6
1 7
if you remove the PARTITION BY clause, then the result is

rownum Col1
1 1
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 3
11 3
12 4
13 5
14 6
15 6
16 7|||You guys rock............

Thanks for the solution.......... Thanks patty, r937 and pdreyer......... gr8 help that was.

Thanks,
Rahul Jha|||SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
INTO #foo
FROM #junqueI had no idea you could insert identity values that way.|||Thanks patty

*laughs out loud*|||:) :) Don Laugh Georgy............... :) :)

I really learnt few things thru this thread.........

Thanks guys once again...... You all are awesome......... Nothing to fear Rahul till the time you guys are there......... :) :) :)

Thanks,
Rahul Jha|||I had no idea you could insert identity values that way.Identity function - I think it is new to 2005. The devs here love it because they love select into too.|||that's a negative poots. the identity function is in SQL 2k. I have used it before.|||My mistake. I came across it about the time we migrated so probably got my wires crossed there.|||well, be careful for what you wish for

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx|||-- Prepare sample data
CREATE TABLE #Sample
(
Col1 INT,
Col2 INT
)

INSERT #Sample
(
Col1
)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 6 UNION ALL
SELECT 7

-- Check precondition
SELECT * FROM #Sample

-- Setup staging mechanism
CREATE CLUSTERED INDEX IX_Sample ON #Sample (Col1)

DECLARE @.Index INT,
@.Col INT

SELECT TOP 1 @.Index = 0,
@.Col = Col1
FROM #Sample
ORDER BY Col1

UPDATE #Sample
SET @.Index = Col2 = CASE WHEN Col1 = @.Col THEN @.Index + 1 ELSE 1 END,
@.Col = Col1

DROP INDEX #Sample.IX_Sample

-- Check postcondition
SELECT * FROM #Sample

-- Clean up
DROP TABLE #Sample

Generate Xml in hierarchical order from DB

Hi

I have a table named UserProfile with following structure :

(TypeIdbigint,TypeNamenvarchar(100),ParentTypeIdbigint)

Have some following sample data :

TypeId TypeName ParentTypeId

1User0

2Artist1

3Singer2

4DJ1

5Band1

6Partner0

7Movies6

8GoodMovies7

9BadMovies7

10Producer6

I want to generate the XML in following form

<Main>

<TypeId>1</TypeId>

<TypeName>User</TypeName>

<ParentTypeId>0</ParentTypeId>

<SubProfiles>

<SubProfile>

<TypeId>2</TypeId>

<TypeName>Artist</TypeName>

<ParentTypeId>1</ParentTypeId>

</SubProfile>

<SubProfile>

<TypeId>4</TypeId>

<TypeName>DJ</TypeName>

<ParentTypeId>1</ParentTypeId>

</SubProfile>

<SubProfile>

<TypeId>5</TypeId>

<TypeName>Band</TypeName>

<ParentTypeId>1</ParentTypeId>

</SubProfile>

</SubProfiles>

</Main>

I am usign CTE for recursive query and then using for xml auto mode to generate xml. But the data is not generating when the parent child relation is in depth level.

Kindly anybody tell me the query so that I can generate the xml in heirarchical order of data.

CREATE FUNCTION dbo.GetProfileSubTree(@.TypeId int)
RETURNS XML
BEGIN RETURN
(SELECT TypeId AS "TypeId",
TypeName AS "TypeName",
ParentTypeId AS "ParentTypeId",
dbo.GetProfileSubTree(TypeId)
FROM UserProfile
WHERE ParentTypeId=@.TypeId
ORDER BY TypeId
FOR XML PATH('SubProfile'),ROOT('SubProfiles'),TYPE)
END

GO

SELECT TypeId AS "TypeId",
TypeName AS "TypeName",
ParentTypeId AS "ParentTypeId",
dbo.GetProfileSubTree(TypeId)
FROM UserProfile
WHERE ParentTypeId=0
ORDER BY TypeId
FOR XML PATH(''),ROOT('Main'),TYPE

|||

Hi,

This solution helps me to generate the heirarchical data

Thanks

|||

Note that SQL Server 2005 has a maximum limit of 32 recursively nested function invocations.

This is mentioned here at

http://msdn2.microsoft.com/en-us/library/ms345137.aspx

Can you suggest any alternate method for unlimited depth?

Generate Xml in hierarchical order from DB

Hi

I have a table named UserProfile with following structure :

(TypeId bigint,TypeNamenvarchar(100),ParentTypeIdbigint)

Have some following sample data :

TypeId TypeName ParentTypeId

1User0

2Artist1

3Singer2

4DJ1

5Band1

6Partner0

7Movies6

8GoodMovies7

9BadMovies7

10Producer6

I want to generate the XML in following form

<Main>

<TypeId>1</TypeId>

<TypeName>User</TypeName>

<ParentTypeId>0</ParentTypeId>

<SubProfiles>

<SubProfile>

<TypeId>2</TypeId>

<TypeName>Artist</TypeName>

<ParentTypeId>1</ParentTypeId>

</SubProfile>

<SubProfile>

<TypeId>4</TypeId>

<TypeName>DJ</TypeName>

<ParentTypeId>1</ParentTypeId>

</SubProfile>

<SubProfile>

<TypeId>5</TypeId>

<TypeName>Band</TypeName>

<ParentTypeId>1</ParentTypeId>

</SubProfile>

</SubProfiles>

</Main>

I am usign CTE for recursive query and then using for xml auto mode to generate xml. But the data is not generating when the parent child relation is in depth level.

Kindly anybody tell me the query so that I can generate the xml in heirarchical order of data.

CREATE FUNCTION dbo.GetProfileSubTree(@.TypeId int)
RETURNS XML
BEGIN RETURN
(SELECT TypeId AS "TypeId",
TypeName AS "TypeName",
ParentTypeId AS "ParentTypeId",
dbo.GetProfileSubTree(TypeId)
FROM UserProfile
WHERE ParentTypeId=@.TypeId
ORDER BY TypeId
FOR XML PATH('SubProfile'),ROOT('SubProfiles'),TYPE)
END

GO

SELECT TypeId AS "TypeId",
TypeName AS "TypeName",
ParentTypeId AS "ParentTypeId",
dbo.GetProfileSubTree(TypeId)
FROM UserProfile
WHERE ParentTypeId=0
ORDER BY TypeId
FOR XML PATH(''),ROOT('Main'),TYPE

|||

Hi,

This solution helps me to generate the heirarchical data

Thanks

|||

Note that SQL Server 2005 has a maximum limit of 32 recursively nested function invocations.

This is mentioned here at

http://msdn2.microsoft.com/en-us/library/ms345137.aspx

Can you suggest any alternate method for unlimited depth?