Thursday, June 6, 2013

[T-SQL] Sql query group by

[T-SQL] Sql query group by

Sql query group by

Posted: 05 Jun 2013 06:59 AM PDT

I have a sql server database table with columns as shown below :[code="sql"]Table1Id Name ErrorCodes1 AB 2 CD 3 AB 394 AB 40[/code]I want to get an output something like this :[code="sql"]Name IdCount ErrorIdCount IdswthErrorCodesAB 3 2 3,4 CD 1 0 0[/code]I wrote a query which looks like this currently :[code="sql"]select Name, Count(Id) as IdCount, Count(Distinct case when ErrorId != ' ' then Id END) as ErrorIdCountfrom Table1 group by Name;[/code]It gives me something like this below :[code="sql"]Name IdCount ErrorIdCount.AB 3 2CD 1 0[/code]I cannot figure out how I can include theIdswthErrorCodes too in my query ? Can anyone point me out how I can solve this ?

How to add a login with db_datareader in a database in a remote server via tsql from the given server?

Posted: 05 Jun 2013 05:56 PM PDT

There is a list of servers and databases kept in the catalog DB ie the DB name and connection details. This information resides in one server. I have to iterate through each server/database and connection details, establish a connection and create a login and a user in that specified database with db_datareader role. I tired using openrowset but I get the following error declare @nameout nvarchar(max)SELECT @NameOut = nameFROM OPENROWSET ('SQLNCLI','Server=Servername;TRUSTED_CONNECTION=YES;','SELECT name FROM sys.server_principals WHERE name = ''Domain\user''')IF ISNULL(@Nameout,'')=''select @Nameout as ifnameBEGINSELECT @NameOut = nameFROM OPENROWSET ('SQLNCLI','Server=Servername;TRUSTED_CONNECTION=YES;','CREATE LOGIN [Domain\user] FROM WINDOWS')select @NameOutENDCannot process the object "CREATE LOGIN [username] FROM WINDOWS". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.The user i have logged in definitely has permission to create login in the target server. I have also tried to link the server but still it throws me the same error. The select with openrowset seem to return the @nameout but I am unable to create a login. Please help me with this issue.

Performance issue imbricate loop

Posted: 05 Jun 2013 05:37 PM PDT

Hello,I have the following query which takes lot of CPU (4 sec) and Duration (4 sec) all the time. I created different index but i have the same problem. I have on noyau.patient 3.4 millions lines and on noyau.LIEU_NAISSANCE 39000 lines.[code="sql"]DECLARE @p__linq__0 nvarchar(4000), @p__linq__1 varchar(8000), @p__linq__2 nvarchar(4000), @p__linq__3 varchar(8000) SET @p__linq__0=N'ARD%'SET @p__linq__1='CHU-LYON'SET @p__linq__2=N'REI%'SET @p__linq__3='CHU-LYON'SELECT TOP (100) [Project1].[pat_id] AS [pat_id], [Project1].[pat_ipp] AS [pat_ipp], [Project1].[pat_nom] AS [pat_nom], [Project1].[pat_nom_naissance] AS [pat_nom_naissance], [Project1].[pat_prenom] AS [pat_prenom], [Project1].[pat_date_naissance] AS [pat_date_naissance], [Project1].[pat_sexe] AS [pat_sexe], [Project1].[pat_date_deces] AS [pat_date_deces], [Project1].[pat_nationalite_id] AS [pat_nationalite_id], [Project1].[pat_categorie_socio_professionnelle] AS [pat_categorie_socio_professionnelle], [Project1].[pat_activite_socio_professionnelle] AS [pat_activite_socio_professionnelle], [Project1].[pat_site_code] AS [pat_site_code], [Project1].[pat_situation] AS [pat_situation], [Project1].[pat_identite_definitive] AS [pat_identite_definitive], [Project1].[lnai_id] AS [lnai_id], [Project1].[pat_ancien_id] AS [pat_ancien_id], [Project1].[pat_portail_id] AS [pat_portail_id], [Project1].[pat_confidentiel] AS [pat_confidentiel], [Project1].[pat_insa] AS [pat_insa], [Project1].[pat_insc] AS [pat_insc], [Project1].[photo_id] AS [photo_id], [Project1].[lnai_id1] AS [lnai_id1], [Project1].[lnai_cp] AS [lnai_cp], [Project1].[lnai_ville] AS [lnai_ville], [Project1].[lnai_pays_id] AS [lnai_pays_id]FROM ( SELECT [Extent1].[pat_id] AS [pat_id], [Extent1].[pat_situation] AS [pat_situation], [Extent1].[pat_ancien_id] AS [pat_ancien_id], [Extent1].[pat_portail_id] AS [pat_portail_id], [Extent1].[pat_ipp] AS [pat_ipp], [Extent1].[pat_nom] AS [pat_nom], [Extent1].[pat_nom_naissance] AS [pat_nom_naissance], [Extent1].[pat_prenom] AS [pat_prenom], [Extent1].[pat_date_naissance] AS [pat_date_naissance], [Extent1].[pat_site_code] AS [pat_site_code], [Extent1].[pat_sexe] AS [pat_sexe], [Extent1].[pat_date_deces] AS [pat_date_deces], [Extent1].[pat_categorie_socio_professionnelle] AS [pat_categorie_socio_professionnelle], [Extent1].[pat_activite_socio_professionnelle] AS [pat_activite_socio_professionnelle], [Extent1].[pat_nationalite_id] AS [pat_nationalite_id], [Extent1].[lnai_id] AS [lnai_id], [Extent1].[pat_identite_definitive] AS [pat_identite_definitive], [Extent1].[pat_confidentiel] AS [pat_confidentiel], [Extent1].[pat_insa] AS [pat_insa], [Extent1].[pat_insc] AS [pat_insc], [Extent1].[photo_id] AS [photo_id], [Extent2].[lnai_id] AS [lnai_id1], [Extent2].[lnai_cp] AS [lnai_cp], [Extent2].[lnai_ville] AS [lnai_ville], [Extent2].[lnai_pays_id] AS [lnai_pays_id] FROM [noyau].[PATIENT] AS [Extent1] LEFT OUTER JOIN [noyau].[LIEU_NAISSANCE] AS [Extent2] ON [Extent1].[lnai_id] = [Extent2].[lnai_id] WHERE ([Extent1].[pat_nom] IS NOT NULL) AND ([Extent1].[pat_nom] LIKE @p__linq__0 ESCAPE '~') AND ([Extent1].[pat_site_code] = @p__linq__1) AND ([Extent1].[pat_nom_naissance] IS NOT NULL) AND ([Extent1].[pat_nom_naissance] LIKE @p__linq__2 ESCAPE '~') AND ([Extent1].[pat_site_code] = @p__linq__3) ) AS [Project1]ORDER BY [Project1].[pat_nom] ASC, [Project1].[pat_nom_naissance] ASC, [project1].[pat_prenom] ASC[/code]I tried to find which part of query takes time and the part taking problem is :[code="sql"]DECLARE @p__linq__0 nvarchar(4000), @p__linq__1 varchar(8000), @p__linq__2 nvarchar(4000), @p__linq__3 varchar(8000) SET @p__linq__0=N'ARD%'SET @p__linq__1='CHU-LYON'SET @p__linq__2=N'REI%'SET @p__linq__3='CHU-LYON'SELECT [Extent1].[pat_id] AS [pat_id], [Extent1].[pat_situation] AS [pat_situation], [Extent1].[pat_ancien_id] AS [pat_ancien_id], [Extent1].[pat_portail_id] AS [pat_portail_id], [Extent1].[pat_ipp] AS [pat_ipp], [Extent1].[pat_nom] AS [pat_nom], [Extent1].[pat_nom_naissance] AS [pat_nom_naissance], [Extent1].[pat_prenom] AS [pat_prenom], [Extent1].[pat_date_naissance] AS [pat_date_naissance], [Extent1].[pat_site_code] AS [pat_site_code], [Extent1].[pat_sexe] AS [pat_sexe], [Extent1].[pat_date_deces] AS [pat_date_deces], [Extent1].[pat_categorie_socio_professionnelle] AS [pat_categorie_socio_professionnelle], [Extent1].[pat_activite_socio_professionnelle] AS [pat_activite_socio_professionnelle], [Extent1].[pat_nationalite_id] AS [pat_nationalite_id], [Extent1].[lnai_id] AS [lnai_id], [Extent1].[pat_identite_definitive] AS [pat_identite_definitive], [Extent1].[pat_confidentiel] AS [pat_confidentiel], [Extent1].[pat_insa] AS [pat_insa], [Extent1].[pat_insc] AS [pat_insc], [Extent1].[photo_id] AS [photo_id], [Extent2].[lnai_id] AS [lnai_id1], [Extent2].[lnai_cp] AS [lnai_cp], [Extent2].[lnai_ville] AS [lnai_ville], [Extent2].[lnai_pays_id] AS [lnai_pays_id] FROM [noyau].[PATIENT] AS [Extent1] WITH (NOLOCK) LEFT OUTER JOIN [noyau].[LIEU_NAISSANCE] AS [Extent2] WITH (NOLOCK) ON [Extent1].[lnai_id] = [Extent2].[lnai_id] WHERE ([Extent1].[pat_nom] IS NOT NULL) AND ([Extent1].[pat_nom] LIKE @p__linq__0 ESCAPE '~') AND ([Extent1].[pat_site_code] = @p__linq__1) AND ([Extent1].[pat_nom_naissance] IS NOT NULL) AND ([Extent1].[pat_nom_naissance] LIKE @p__linq__2 ESCAPE '~') AND ([Extent1].[pat_site_code] = @p__linq__3)[/code]I don't know I can improve it.:hehe:Thanks for your help,EC

Data type Question

Posted: 05 Jun 2013 10:21 PM PDT

VARCHAR2 (4000 Byte) in oracle What can be used in sql server 2008r2VARCHAR(4000)?

JOIN Result

Posted: 05 Jun 2013 04:35 PM PDT

Hi, yesterday I was working on a scenario in which I have 3 tables Table1, Table2, Table3. Table3 contain data from table1 and table2 so instead of writing two seperate queries I write in a singled query, but ir doesn't return any rows. I dont know why ?Please find the scripts below and tell me is this possible or not ?Create table #A (ID int identity primary key, Col1 varchar(10))Create table #B (ID int identity primary key, Col2 varchar(10))Create table #C (ID int identity primary key, Col3 varchar(10))INSERT INTO #ASELECT 'A1'UNION ALLSELECT 'A2'UNION ALLSELECT 'A3'INSERT INTO #BSELECT 'B1'UNION ALLSELECT 'B2'UNION ALL SELECT 'B3' INSERT INTO #CSELECT 'A1'UNION ALLSELECT 'B2'SELECT a.Col1, b.Col2FROM #A aJOIN #C C ON c.Col3 = a.Col1JOIN #B b ON b.Col2 = c.Col3DROP TABLE #ADROP TABLE #BDROP TABLE #C

sp_OAGetProperty @obj, 'responseText', @response OUT cannot handle large output

Posted: 13 Jul 2011 08:28 PM PDT

Hi All,This is quite weird, so thought you can have some ideas, please:I use HTTP POST to send and XML file to my system and to get another XML as a response.I found out that if a response XML is bigger than X characters ( haven't found yet how much is X)my response fails. I defined @response as VARCHAR(MAX) and I use SQL 2008.I know that on a server side the created XML is of a correct structure, it just fails to pull it back if it goes over the X size.Any ideas, please?Thanks.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog