Problem in giving Database Access to a new user

I create a new user and give them a password and hten confirm as
normal. When I subsequently try to give this user access to the
database then I am prompted to 'Confirm Password' jus as I was when I
created the user. I've not seen thi before. Can anyone shed some light
please?
John
Hi,
(Information taken from old post from Kevin Micosoft)
This was a bug with the security rollup for SQL. You need to install build
819. Have a look into the below link:-
http://support.microsoft.com/default.aspx?kbid=826161
Thanks
Hari
MCDBA
"John Lundrigan" <jonel@.glenavon1.demon.co.uk> wrote in message
news:s1ldc01o9nnrsoil1cmep2j23p49eaiise@.4ax.com...
> I create a new user and give them a password and hten confirm as
> normal. When I subsequently try to give this user access to the
> database then I am prompted to 'Confirm Password' jus as I was when I
> created the user. I've not seen thi before. Can anyone shed some light
> please?
> John

Problem in giving Database Access to a new user

I create a new user and give them a password and hten confirm as
normal. When I subsequently try to give this user access to the
database then I am prompted to 'Confirm Password' jus as I was when I
created the user. I've not seen thi before. Can anyone shed some light
please?
JohnHi,
(Information taken from old post from Kevin Micosoft)
This was a bug with the security rollup for SQL. You need to install build
819. Have a look into the below link:-
http://support.microsoft.com/default.aspx?kbid=826161
Thanks
Hari
MCDBA
"John Lundrigan" <jonel@.glenavon1.demon.co.uk> wrote in message
news:s1ldc01o9nnrsoil1cmep2j23p49eaiise@.4ax.com...
> I create a new user and give them a password and hten confirm as
> normal. When I subsequently try to give this user access to the
> database then I am prompted to 'Confirm Password' jus as I was when I
> created the user. I've not seen thi before. Can anyone shed some light
> please?
> Johnsql

Problem in giving Database Access to a new user

I create a new user and give them a password and hten confirm as
normal. When I subsequently try to give this user access to the
database then I am prompted to 'Confirm Password' jus as I was when I
created the user. I've not seen thi before. Can anyone shed some light
please?
JohnHi,
(Information taken from old post from Kevin Micosoft)
This was a bug with the security rollup for SQL. You need to install build
819. Have a look into the below link:-
http://support.microsoft.com/default.aspx?kbid=826161
Thanks
Hari
MCDBA
"John Lundrigan" <jonel@.glenavon1.demon.co.uk> wrote in message
news:s1ldc01o9nnrsoil1cmep2j23p49eaiise@.
4ax.com...
> I create a new user and give them a password and hten confirm as
> normal. When I subsequently try to give this user access to the
> database then I am prompted to 'Confirm Password' jus as I was when I
> created the user. I've not seen thi before. Can anyone shed some light
> please?
> John

Problem in DTS

Hi ,
I have a transaction table in my ERP Database.
The table size is 3 GB means (50,000,00)*6 records
Now i am transfering to Access database but it give error
exceeds maximium number of rows.
Can any one tell me how can i tarnsfer the data.I dont want to transfer
data to bcp file.
i have sp3 installed on my machine.
from
KillerWhat is creating the error, Access or the tool you are using for the
transfer? DTS should be able to do this transfer for you without any
problems.
--Brian
(Please reply to the newsgroups only.)
"doller" <sufianarif@.gmail.com> wrote in message
news:1125672105.203993.37080@.g49g2000cwa.googlegroups.com...
> Hi ,
> I have a transaction table in my ERP Database.
> The table size is 3 GB means (50,000,00)*6 records
> Now i am transfering to Access database but it give error
> exceeds maximium number of rows.
> Can any one tell me how can i tarnsfer the data.I dont want to transfer
> data to bcp file.
> i have sp3 installed on my machine.
> from
> Killer
>|||This is not a SQL Server issue but an Access one. According to
http://support.microsoft.com/defaul...b;en-us;302524, Access has a
2GB limitation.
Hope this helps.
Dan Guzman
SQL Server MVP
"doller" <sufianarif@.gmail.com> wrote in message
news:1125672105.203993.37080@.g49g2000cwa.googlegroups.com...
> Hi ,
> I have a transaction table in my ERP Database.
> The table size is 3 GB means (50,000,00)*6 records
> Now i am transfering to Access database but it give error
> exceeds maximium number of rows.
> Can any one tell me how can i tarnsfer the data.I dont want to transfer
> data to bcp file.
> i have sp3 installed on my machine.
> from
> Killer
>

Problem in DTS

Hi ,
I have a transaction table in my ERP Database.
The table size is 3 GB means (50,000,00)*6 records
Now i am transfering to Access database but it give error
exceeds maximium number of rows.
Can any one tell me how can i tarnsfer the data.I dont want to transfer
data to bcp file.
i have sp3 installed on my machine.
from
Killer
What is creating the error, Access or the tool you are using for the
transfer? DTS should be able to do this transfer for you without any
problems.
--Brian
(Please reply to the newsgroups only.)
"doller" <sufianarif@.gmail.com> wrote in message
news:1125672105.203993.37080@.g49g2000cwa.googlegro ups.com...
> Hi ,
> I have a transaction table in my ERP Database.
> The table size is 3 GB means (50,000,00)*6 records
> Now i am transfering to Access database but it give error
> exceeds maximium number of rows.
> Can any one tell me how can i tarnsfer the data.I dont want to transfer
> data to bcp file.
> i have sp3 installed on my machine.
> from
> Killer
>
|||This is not a SQL Server issue but an Access one. According to
http://support.microsoft.com/default...;en-us;302524, Access has a
2GB limitation.
Hope this helps.
Dan Guzman
SQL Server MVP
"doller" <sufianarif@.gmail.com> wrote in message
news:1125672105.203993.37080@.g49g2000cwa.googlegro ups.com...
> Hi ,
> I have a transaction table in my ERP Database.
> The table size is 3 GB means (50,000,00)*6 records
> Now i am transfering to Access database but it give error
> exceeds maximium number of rows.
> Can any one tell me how can i tarnsfer the data.I dont want to transfer
> data to bcp file.
> i have sp3 installed on my machine.
> from
> Killer
>
sql

Problem in DTS

Hi ,
I have a transaction table in my ERP Database.
The table size is 3 GB means (50,000,00)*6 records
Now i am transfering to Access database but it give error
exceeds maximium number of rows.
Can any one tell me how can i tarnsfer the data.I dont want to transfer
data to bcp file.
i have sp3 installed on my machine.
from
KillerWhat is creating the error, Access or the tool you are using for the
transfer? DTS should be able to do this transfer for you without any
problems.
--
--Brian
(Please reply to the newsgroups only.)
"doller" <sufianarif@.gmail.com> wrote in message
news:1125672105.203993.37080@.g49g2000cwa.googlegroups.com...
> Hi ,
> I have a transaction table in my ERP Database.
> The table size is 3 GB means (50,000,00)*6 records
> Now i am transfering to Access database but it give error
> exceeds maximium number of rows.
> Can any one tell me how can i tarnsfer the data.I dont want to transfer
> data to bcp file.
> i have sp3 installed on my machine.
> from
> Killer
>|||This is not a SQL Server issue but an Access one. According to
http://support.microsoft.com/default.aspx?scid=kb;en-us;302524, Access has a
2GB limitation.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"doller" <sufianarif@.gmail.com> wrote in message
news:1125672105.203993.37080@.g49g2000cwa.googlegroups.com...
> Hi ,
> I have a transaction table in my ERP Database.
> The table size is 3 GB means (50,000,00)*6 records
> Now i am transfering to Access database but it give error
> exceeds maximium number of rows.
> Can any one tell me how can i tarnsfer the data.I dont want to transfer
> data to bcp file.
> i have sp3 installed on my machine.
> from
> Killer
>

Problem in connecting to SSAS

I'm facing some security when deploying SSAS and reports using Reporting Services. In my environment, I don't understand why I can access the SSAS via the management studio if I don't have the domain administrator right. The support team have granted this right to me for development purpose.
In the connection dialog of Management Studio, I can only choose Server name after I click Connect --> Analysis Services. The authentication type is dimmed out by default. Please gives me some more relevant links to setup properly.
After deployed my cubes and reports, the other user cannot view the reports as they can't access the SSAS database. What should I do? There're so many security setting and I don't know which one I should tune. Thanks in advance.

Hello! You do not need to be an domain adminstrator in order to access Reporting Services and Analysis Services. It is sufficient to be a local administrator on the server running these two applications.

In Reporting Services you have the option to run a report with windows authentication or an assigned account for Reporting Services access to Analysis Services.

If you run with the first option each user need to mapped to a role in Analysis Services which can be done with individual accounts or windows groups. For the second option it is only the Reporting Services(AS2005) account that needs to mapped to a role in Analysis Services.

You can do this both in BIDS, Report Manager or Management Studio.

This is true for a server where both these applications are running. If Reporting Services is on a separate server things are a little bit more complicated. In this case you will have to set up account delegation in Active Directory. The key word for this setup is 'Kerberos' .

HTH

Thomas Ivarsson

|||Can you give me some more detail on the configuration please? Are there any guides available on the web? Thanks!|||

Hello! I have not found any best official practices regarding SSRS2005 and SSAS2005 integration.

My advice is based on customer cases/practises that I have used and some good writing in a reporting services book.

Regards

Thomas Ivarsson

|||http://technet.microsoft.com/en-us/library/ms175386(SQL.90).aspx

This website provides what I needed.

Problem in connecting to SSAS

I'm facing some security when deploying SSAS and reports using Reporting Services. In my environment, I don't understand why I can access the SSAS via the management studio if I don't have the domain administrator right. The support team have granted this right to me for development purpose.
In the connection dialog of Management Studio, I can only choose Server name after I click Connect --> Analysis Services. The authentication type is dimmed out by default. Please gives me some more relevant links to setup properly.
After deployed my cubes and reports, the other user cannot view the reports as they can't access the SSAS database. What should I do? There're so many security setting and I don't know which one I should tune. Thanks in advance.

Hello! You do not need to be an domain adminstrator in order to access Reporting Services and Analysis Services. It is sufficient to be a local administrator on the server running these two applications.

In Reporting Services you have the option to run a report with windows authentication or an assigned account for Reporting Services access to Analysis Services.

If you run with the first option each user need to mapped to a role in Analysis Services which can be done with individual accounts or windows groups. For the second option it is only the Reporting Services(AS2005) account that needs to mapped to a role in Analysis Services.

You can do this both in BIDS, Report Manager or Management Studio.

This is true for a server where both these applications are running. If Reporting Services is on a separate server things are a little bit more complicated. In this case you will have to set up account delegation in Active Directory. The key word for this setup is 'Kerberos' .

HTH

Thomas Ivarsson

|||Can you give me some more detail on the configuration please? Are there any guides available on the web? Thanks!|||

Hello! I have not found any best official practices regarding SSRS2005 and SSAS2005 integration.

My advice is based on customer cases/practises that I have used and some good writing in a reporting services book.

Regards

Thomas Ivarsson

|||http://technet.microsoft.com/en-us/library/ms175386(SQL.90).aspx

This website provides what I needed.sql

problem in connecting to MSDE database from the same domain

Hi!
I have installed MSDE 2000 in "Mixed Windows and SQL mode authentication with network access" .following is the command:

setup.exe DISABLENETWORKPROTOCOLS=0 SAPWD="password" INSTANCENAME="TempInstance" SECURITYMODE=SQL

Then I created the database TempDB in it.

When I try to connect this database from my program running on my machine , it works fine. But when i try to connect to it from the same program running on a different machine on the same domain.It gives error:

"SQL Server does not exist or access denied ."

Even when i run the same program in my machine to access the same TempDB database on a remote machine that is not in my domain, it works fine.

this is my connection string:
Data Source={Database server's IP Address}\TempInstance;Initial Catalog=TempDB;User ID=sa;PWD=password;

Please help.

KT.What's the operating system for the client from which you are unsuccessfully attempting to connect? What version of MDAC is running on it?

Also, be aware that a named instance install of MSDE will set up to run on a non-default port (ie, not 1433). You can verify your connectivity by determining what port the SQL listener is running on and then using telnet {ip address} {port} from your client machine. You may need to account for the alternate port on your client machine. You can set up an alias for it by running cliconfg at a command prompt on your client machine.

Regards,

hmscott|||Thanks hmscott!

Actually my problem was caused by windows firewall.

regards.
Kuldeep.

Problem in connecting 2 databases with sql Server 2005 Express

I am programming in VB6 using ADO 2.8. This connection and query works in MS access, SQL server 2000 and Sql Server 2005. does not work in Sql server 2005 express. Any Suggestions?

Connection String #1 Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName=C:\Program Files\Material_Management_System\DATA\Main.mdf;Data Source=Steve_Laptop\sqlexpress

Connection String #2 Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName=C:\Program Files\Material_Management_System\DATA\Items.mdf;Data Source=Steve_Laptop\sqlexpress

Sql Query: Select POLINE.ID as POLine_ID, PFMS.ID as Items_ID FROM POLINE LEFT JOIN Items.PFMS as PFMS ON POLINE.lItem_ID = PFMS.ID

Error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Items.PFMS'

Sql Query#2: Select POLINE.ID as POLine_ID, PFMS.ID as Items_ID FROM POLINE LEFT JOIN Items.dbo.PFMS as PFMS ON POLINE.lItem_ID = PFMS.ID

Error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Items.dbo.PFMS'.

I am running the queries directly from the 2005 Mgt window to take as many variables out of the equasion. I get the same error in Visual basic

How am I supposed to reference a join of 2 databases? Any suggestions

I did not try that yet, but I think the cross join between user instances is not possible, you either have to do an in-memory join in .NET or attach the databases to the SQL Server Express instance and then join them using the three part name.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Problem in accessing the database(Sqlserver 2005) from client machine

Hi

We have upgraded sql server 2000 to sqlserver 2005 on the development server.

To access the database server, we installed the sql server management studio on the client PC with Admin Account. The .Net framework and sqlserver management studio were installed and I am able to access the database from the client PC using Admin Login. Then we logged off and tried to login as user account on the client PC. From the user account login we are unable to open the sql server management studio. The error is as follows

“The proper type library could not be found in system registry”

The client PC configuration : windows 2000 Professional ( sp4)

Regards,

K.S.Subba Rao

Hi K.S. Subba,

What is probably happening is the non-Admin user that you are logging on with does not have permissions to read the appropriate type library informations for SQL-specific COM components from registry. The fastest approach you can take to troubleshoot that is to use RegMon from http://www.microsoft.com/technet/sysinternals/SystemInformation/Regmon.mspx, find out which keys are "Access Denied" or "Not Found" and when logged as Admin, grant the permissions to those to the non-Admin user. As a lazy alternative you could try to grant permissions to the user to HKEY_LOCAL_MACHINE\SOFTWARE\Classes and all the subcomponents, but that might expose a security risk.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Problem in accessing reporting services web service, unable to set credentials I guess....

I am trying to access the Reporting Services Web Service, but i m not able to make it work. i tried with default credentials and network credentials

ReportingService

service =newReportingService();

service.Credentials =new System.Net.NetworkCredential("dbo","coreobject5","");

service.CreateSubscription(report, extSettings, description, eventType, matchData, parameters);

This is the exception i get

The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. --> The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Web.Services.Protocols.SoapException: The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. --> The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database.

Source Error:

Line 3382: [return: System.Xml.Serialization.XmlElementAttribute("SubscriptionID")]Line 3383: public string CreateSubscription(string Report, ExtensionSettings ExtensionSettings, string Description, string EventType, string MatchData, ParameterValue[] Parameters) {Line 3384: object[] results = this.Invoke("CreateSubscription", new object[] {Line 3385: Report,Line 3386: ExtensionSettings,

Tried to solve this and found that its the problem with the datasource I created for the report, tried to use 'sa' user in

data source->credentials->Use a specified user name and password, but did'nt worked.

One more thing i am able to accessGetExtensionSettings("Report Server Fileshare");API with

service.Credentials = System.Net.CredentialCache.DefaultCredentials;

Not able to find out what users to create in Report Server database or the database i am connecting to?

Will appreciate any kind of help.

Problem in accessing reporting services web service, unable to set credentials I guess....

I am trying to access the Reporting Services Web Service, but i m not able to make it work. i tried with default credentials and network credentials

ReportingService service = new ReportingService();

service.Credentials = new System.Net.NetworkCredential("username", "password", "domain");

service.CreateSubscription(report, extSettings, description, eventType, matchData, parameters);

This is the exception i get

The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. > The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Web.Services.Protocols.SoapException: The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. > The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database.
Source Error:

Line 3382: [return: System.Xml.Serialization.XmlElementAttribute("SubscriptionID")] Line 3383: public string CreateSubscription(string Report, ExtensionSettings ExtensionSettings, string Description, string EventType, string MatchData, ParameterValue[] Parameters) { Line 3384: object[] results = this.Invoke("CreateSubscription", new object[] { Line 3385: Report, Line 3386: ExtensionSettings,

Tried to solve this and found that its the problem with the datasource I created for the report, tried to use 'sa' user in

data source->credentials->Use a specified user name and password, but did'nt worked.

Onemore thing i am able to access GetExtensionSettings("Report Server Fileshare"); API with

service.Credentials = System.Net.CredentialCache.DefaultCredentials;

Not able to find out what users to create in Report Server database or the database i am connecting to?

Will appreciate any kind of help.

|||

I believe your problem does not lie with a user in a database; I have used RS WS to Create / Fire Subscriptions and to UpdateSnapshots. Whatever user you pass to the Web Service must have Generate Events permission under Site Settings. Give the user those permissions and I think you will find that it will resolve your problem

Please let me know

Thanks

|||Report's data source must use stored credentials.sql

Problem in accessing reporting services web service, unable to set credentials I guess....

I am trying to access the Reporting Services Web Service, but i m not able to make it work. i tried with default credentials and network credentials

ReportingService service = new ReportingService();

service.Credentials = new System.Net.NetworkCredential("username", "password", "domain");

service.CreateSubscription(report, extSettings, description, eventType, matchData, parameters);

This is the exception i get

The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. > The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Web.Services.Protocols.SoapException: The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. > The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database.
Source Error:

Line 3382: [return: System.Xml.Serialization.XmlElementAttribute("SubscriptionID")] Line 3383: public string CreateSubscription(string Report, ExtensionSettings ExtensionSettings, string Description, string EventType, string MatchData, ParameterValue[] Parameters) { Line 3384: object[] results = this.Invoke("CreateSubscription", new object[] { Line 3385: Report, Line 3386: ExtensionSettings,

Tried to solve this and found that its the problem with the datasource I created for the report, tried to use 'sa' user in

data source->credentials->Use a specified user name and password, but did'nt worked.

Onemore thing i am able to access GetExtensionSettings("Report Server Fileshare"); API with

service.Credentials = System.Net.CredentialCache.DefaultCredentials;

Not able to find out what users to create in Report Server database or the database i am connecting to?

Will appreciate any kind of help.

|||

I believe your problem does not lie with a user in a database; I have used RS WS to Create / Fire Subscriptions and to UpdateSnapshots. Whatever user you pass to the Web Service must have Generate Events permission under Site Settings. Give the user those permissions and I think you will find that it will resolve your problem

Please let me know

Thanks

|||Report's data source must use stored credentials.

problem in accessing report manager and server

hi,

i installed RS 2005 on windows XP with local system account,report manager does not access.it gives

The page cannot be displayed

HTTP 500 - Internal server error
Internet Explorer

although reportmanager and reportserver directory is available in its default location.

and also when i clicked on any report on report server it gives

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help

Requested registry access is not allowed.

i am using my account having admin privlge

please help me.....

Amit,

Could you, please, take a look at the log files for the windows service and for the web service and let me know what errors you find there?

The RS log files are located here:

%Program Files%\Microsoft SQL Server\MSSQL.X\Reporting Services\LogFiles

The log files for the RS windows service look like this: ReportServerService_(timestamp).log and the log files for the RS web service look like this: ReportServer_(timestamp).log.

Mihaela

problem in accessing report manager and server

hi,

i installed RS 2005 on windows XP with local system account,report manager does not access.it gives

The page cannot be displayed

HTTP 500 - Internal server error
Internet Explorer

although reportmanager and reportserver directory is available in its default location.

and also when i clicked on any report on report server it gives

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help

Requested registry access is not allowed.

i am using my account having admin privlge

please help me.....

Amit,

Could you, please, take a look at the log files for the windows service and for the web service and let me know what errors you find there?

The RS log files are located here:

%Program Files%\Microsoft SQL Server\MSSQL.X\Reporting Services\LogFiles

The log files for the RS windows service look like this: ReportServerService_(timestamp).log and the log files for the RS web service look like this: ReportServer_(timestamp).log.

Mihaela

Problem in a CROSS to SQL

Dear friends;

I have a CROSS table in ACCESS that I want to export to SQL 2005. The query is:

TRANSFORM Count(GD_PROFILE.cnRow) AS CountOfcnRow

SELECT GD_PROFILE.ID

FROM GD_PROFILE

GROUP BY GD_PROFILE.ID

PIVOT GD_PROFILE.ID_APLIC;

The RESULT is:

ID

20

22

77

122

141

179

211

327

334

370

388

405

524

584

645

687

826

865

914

CN1

1

1

1

1

1

CN2

1

1

1

1

CN3

1

1

1

1

CN4

1

1

1

1

1

CN5

1

1

1

1

CN8

1

1

1

1

1

1

1

1

1

1

1

CN14

1

1

1

1

1

1

1

1

CN15

1

1

1

1

1

1

1

1

1

1

CN22

1

1

1

1

1

1

1

1

CN29

1

1

1

1

1

1

1

CN36

1

1

1

I want to create a query in SQL Server that give me rows from the cross query that are equal. For example, the row 1 and 4 are equal. Has the same values in each row. How can I make it programatically?

The Cross is a simple cross query, and the goal is return this final result:

Profile 1 - ID CN1, ID CN4

Profile 2 - ID CN2, ID CN3, ID CN5

Profile 3 - ID CN8

Profile 4 - ID CN14

Profile 5 - ID CN15

Profile 6 - ID CN22

Profile 7 - ID CN29

Profile 8 - ID CN36

Could you HELP me?
THANKS!!!!

HELP me please!!!!

I need it soon!!

Thanks!!!!!!!

|||

--For SQL Server 2005:

SELECT ROW_Number() OVER(ORDER BY (pvt.[1] + coalesce(', ' + pvt.[2], '') + coalesce(', ' + pvt.[3], ''))) AS Profile,

pvt.[1] + coalesce(', ' + pvt.[2], '') + coalesce(', ' + pvt.[3], '') as merged

FROM(SELECT ID, ROW_Number() OVER (PARTITION BY coalesce (',' + [F2] , '-') + + coalesce(',' + [F3], '-') + coalesce(',' + [F4], '-' ) + coalesce(',' + [F5], '-') + coalesce(',' + [F6], '-' ) + + coalesce (',' + [F7] , '-') + coalesce(',' + [F8], '-') +coalesce(',' + [F9], '-') + coalesce(',' + [F10], '-' ) + + coalesce (',' + [F11] , '-') + coalesce(',' + [F12], '-') + coalesce(',' + [F13], '-') + coalesce(',' + [F14], '-' ) + + coalesce (',' + [F15] , '-') + coalesce(',' + [F16], '-') +coalesce(',' + [F17], '-') + coalesce(',' + [F18], '-') + + coalesce( ',' + [F19], '-') + coalesce (',' + [F20] , '-') ORDER BY ID) AS rowNum_ID,

coalesce(',' + [F2], '-') + + coalesce( ',' + [F3], '-') + coalesce (',' + [F4] , '-') + coalesce(',' + [F5], '-') + coalesce(',' + [F6], '-' ) + + coalesce (',' + [F7] , '-') + coalesce(',' + [F8], '-') + coalesce(',' + [F9], '-') + coalesce(',' + [F10], '-' ) + + coalesce (',' + [F11] , '-') + coalesce(',' + [F12], '-') + coalesce(',' + [F13], '-') + coalesce(',' + [F14], '-' ) + + coalesce (',' + [F15] , '-') + coalesce(',' + [F16], '-') + coalesce(',' + [F17], '-') + coalesce(',' + [F18], '-') + + coalesce( ',' + [F19], '-') + coalesce (',' + [F20] , '-') AS newlabel

FROM yourTable) as t1

pivot (min(ID) for rowNum_ID in ([1], [2], [3]))AS pvt

ORDER BY pvt.[1] + coalesce(', ' + pvt.[2], '') + coalesce(', ' + pvt.[3], '')

CREATE TABLE yourTable(

[ID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F3] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F4] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F5] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F6] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F7] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F8] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F9] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F10] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F11] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F12] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F13] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F14] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F15] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F16] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F17] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F18] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F19] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[F20] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

)

INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN1',NULL,'1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,'1','1',NULL,NULL,'1',NULL,NULL)

INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN2',NULL,'1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,NULL,'1',NULL,NULL,'1',NULL,NULL)

INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN3',NULL,'1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,NULL,'1',NULL,NULL,'1',NULL,NULL)

INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN4',NULL,'1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,'1','1',NULL,NULL,'1',NULL,NULL)

INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN5',NULL,'1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,NULL,'1',NULL,NULL,'1',NULL,NULL)

INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN8','1','1',NULL,'1','1',NULL,NULL,NULL,NULL,'1','1',NULL,NULL,'1','1','1','1','1',NULL)

INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN14','1','1',NULL,'1','1',NULL,NULL,NULL,NULL,'1','1',NULL,NULL,'1',NULL,NULL,'1',NULL,NULL)

INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN15','1','1','1','1','1',NULL,'1',NULL,NULL,'1','1',NULL,NULL,'1',NULL,NULL,'1',NULL,NULL)

INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN22',NULL,'1',NULL,NULL,NULL,'1',NULL,NULL,'1','1',NULL,'1','1','1',NULL,NULL,'1',NULL,NULL)

INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN29','1','1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,'1',NULL,'1',NULL,NULL,'1',NULL,'1')

INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN36',NULL,NULL,NULL,'1',NULL,NULL,NULL,'1',NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,NULL,NULL,NULL)

|||

Dear Friend!

First of all I want to thank your help.

The Grid above is a result of a CROSS query based in this table:

ID

ID_APLIC

CN1

826

CN1

584

CN1

524

CN1

370

CN1

22

CN2

826

CN2

584

CN2

370

CN2

22

CN3

826

CN3

584

CN3

370

CN3

22

CN4

826

CN4

584

CN4

524

CN4

370

CN4

22

CN5

826

CN5

584

CN5

370

CN5

22

CN8

865

CN8

826

CN8

687

CN8

645

CN8

584

CN8

388

CN8

370

CN8

141

CN8

122

CN8

22

CN8

20

CN14

826

CN14

584

CN14

388

CN14

370

CN14

141

CN14

122

CN14

22

CN14

20

CN15

826

CN15

584

CN15

388

CN15

370

CN15

211

CN15

141

CN15

122

CN15

77

CN15

22

CN15

20

CN22

826

CN22

584

CN22

524

CN22

405

CN22

370

CN22

334

CN22

179

CN22

22

CN29

914

CN29

826

CN29

584

CN29

405

CN29

370

CN29

22

CN29

20

CN36

584

CN36

327

CN36

122

What I want is to check the CN (Computernames) that have the same profile. Example, I know that CN1 and CN4 has the same aplications, the profile one.

For each CN I need to know if the profile for this CN already exists, if true insert the reference in Computername Table, and if false, create new Profile in table Profile and the aplicationIDs in Profile_AplicID Table.

Profile

ID_Profile

Nome

1

Profile 1

2

Profile 2

3

Profile 3

4

Profile 4

Profile_AplicID

AplicID_Profile

ID_Profile

22

1

370

1

524

1

584

1

826

1

Computername

CN_ID

ID_Profile

CN1

1

CN4

1

…

…

COULD YOU HELP ME?!!
THANKS FRIEND!!!

|||

Anyone need more help to understand my problem?
Thanks!!!

|||Anyonne there?