Saturday, July 13, 2013

[T-SQL] ALTER SERVER ROLE syntax error

[T-SQL] ALTER SERVER ROLE syntax error


ALTER SERVER ROLE syntax error

Posted: 07 Aug 2012 10:08 PM PDT

HI allI am trying to run the following but I get a syntax near 'ROLE' error, but it looks ok to me, any ideas? I am using SQL 2012 SSMS against a SQL 2008 R2 instance.[code="sql"]CREATE LOGIN [xxx\xxx] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]GOALTER SERVER ROLE [sysadmin] ADD MEMBER [xxx\xxx]GO[/code]

complex joins retrive the echivalent results

Posted: 12 Jul 2013 07:58 PM PDT

HelloI have a problem on doing an complex join between multiple tables :Here is my tables [code="sql"]GLCMSIDN KMSO KCVA FSCVA---- ---- ---- -----0099 0001 0007 N0099 0001 000D A0099 0003 0007 A0099 0003 000D A0099 0009 0007 A0099 000A 000D AGLMSOIDN KMSO KMSOP KARA COD NOM DNAS DINIV DFINV DFINA FAB UPS UPD KTDM---- ---- ----- ---- -------------------- ------------------------------------------------------------------------------------------------------------------------ -------- -------- -------- -------- ---- ---------------- ------------ ----0099 0003 NULL 01 asdasd asdasdas 00000000 00000000 99999999 99999999 A admin 201307101544 020099 0006 NULL 02 ModuleSoftware2 NormeCondition2 00000000 00000000 99999999 99999999 A admin 201307101214 010099 0007 NULL 01 Impersonaneeee Pendice 00000000 00000000 99999999 99999999 A admin 201307101230 010099 0009 NULL 02 Prodologia 34234234234 00000000 00000000 99999999 99999999 A admin 201307101645 010099 000A NULL 02 ProblemoFiltre BenjGLARAIDN KMSO KMSOP KARA COD NOM DNAS DINIV DFINV DFINA FAB UPS UPD KTDM---- ---- ----- ---- -------------------- ------------------------------------------------------------------------------------------------------------------------ -------- -------- -------- -------- ---- ---------------- ------------ ----0099 0003 NULL 01 asdasd asdasdas 00000000 00000000 99999999 99999999 A admin 201307101544 020099 0006 NULL 02 ModuleSoftware2 NormeCondition2 00000000 00000000 99999999 99999999 A admin 201307101214 010099 0007 NULL 01 Impersonaneeee Pendice 00000000 00000000 99999999 99999999 A admin 201307101230 010099 0009 NULL 02 Prodologia 34234234234 00000000 00000000 99999999 99999999 A admin 201307101645 010099 000A NULL 02 ProblemoFiltre BenjGLPSIIDN KPSI NOM DNAS ORD FAB SIG UPS UPD DESCR COD NTE---- ---- -------------------------------------------------------------------------------- -------- ----------- ---- -------------------- ---------------- ------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0099 01 Pacchetto 1 00000000 0 A EMEPS1 admin 201306121059 Il pacchetto 1 PS1 sdsdafdsa0099 02 Pacchetto 2 00000000 0 A EMEPS2 admin 201306121116 Questo è il pacchetto 2 PS2 NULLGLTDMIDN KTDM SIG NOM DTDM IMG FAB UPS UPD---- ---- -------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ---------------- ------------0099 01 MAR1 Marchio 1 Questo è il marchio 1 NULL A admin 2013061210300099 02 MAR2 Marchio 2 Questo è il marchio 2 0x89504E470D0A1A0A0000000D494844520000006000000048080200000086056734000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA86400003B2C49444154785E5D7C07785555BAF6997B71140B9D0001D27B4F486F1048EFBD27E7242727E79C9CDE A admin 201306121055GLCVAIDN KCVA KCTR DCVA FSCVA---- ---- ---- -------------------------------------------------------------------------------- -----0099 0001 01 Distribuzione A0099 0002 01 Vendita A0099 0003 02 Energia A0099 0004 02 Acqua A0099 0005 02 Gas A0099 0007 07 aaa A0099 0008 06 v2 A0099 0009 08 aaaa A0099 000A 08 yyyyjjjjj A0099 000B 04 aa A0099 000C 04 bb A0099 000D 06 v3 A0099 000E 06 v4 AGLCTRIDN KCTR DCAT XMUL FSCTR FAP---- ---- -------------------------------------------------------------------------------- ---- ----- ----0099 01 Settore X A C0099 02 Servizio X A C0099 03 EttoreCust1 NULL A C0099 04 EttoreCust2 X A C0099 06 Eme3Prodcat2x X A P0099 07 Eme4Prod NULL A P0099 08 sss X A P0099 0C AAA NULL A P[/code]Here is my sql query which is wrong on ,,AND ( SELECT COUNT(*) ..... '':[code="sql"]DECLARE @kpsi AS VarChar (2) DECLARE @diniv AS VarChar (8) DECLARE @dfina AS VarChar (8) DECLARE @fab AS VarChar (1) DECLARE @idn AS VarChar (4) SET @kpsi = '02' SET @diniv = '00000000' SET @dfina = '99999999' SET @fab = 'A' SET @idn = '0099' select distinct GLPSI.IDN GLMSO_GLPSI_IDN, GLPSI.KPSI GLMSO_GLPSI_KPSI, GLPSI.SIG GLMSO_GLPSI_SIG, GLPSI.NOM GLMSO_GLPSI_NOM, GLPSI.DESCR GLMSO_GLPSI_DESCR, GLPSI.DNAS GLMSO_GLPSI_DNAS, GLPSI.ORD GLMSO_GLPSI_ORD, GLPSI.FAB GLMSO_GLPSI_FAB, GLPSI.NTE GLMSO_GLPSI_NTE, GLPSI.COD GLMSO_GLPSI_COD, GLPSI.UPS GLMSO_GLPSI_UPS, GLPSI.UPD GLMSO_GLPSI_UPD, GLARA.IDN GLMSO_GLARA_IDN, GLARA.KARA GLMSO_GLARA_KARA, GLARA.KPSI GLMSO_GLARA_KPSI, GLARA.SIG GLMSO_GLARA_SIG, GLARA.NOM GLMSO_GLARA_NOM, GLARA.DNAS GLMSO_GLARA_DNAS, GLARA.ORD GLMSO_GLARA_ORD, GLARA.FAB GLMSO_GLARA_FAB, GLARA.UPS GLMSO_GLARA_UPS, GLARA.UPD GLMSO_GLARA_UPD, GLTDM.IDN GLMSO_GLTDM_IDN, GLTDM.KTDM GLMSO_GLTDM_KTDM, GLTDM.SIG GLMSO_GLTDM_SIG, GLTDM.NOM GLMSO_GLTDM_NOM, GLTDM.DTDM GLMSO_GLTDM_DTDM, GLTDM.FAB GLMSO_GLTDM_FAB, GLTDM.UPS GLMSO_GLTDM_UPS, GLTDM.UPD GLMSO_GLTDM_UPD, GLMSO.IDN GLMSO_IDN, GLMSO.KMSO GLMSO_KMSO, GLMSO.KMSOP GLMSO_KMSOP, GLMSO.KARA GLMSO_KARA, GLMSO.COD GLMSO_COD, GLMSO.NOM GLMSO_NOM, GLMSO.DNAS GLMSO_DNAS, GLMSO.DINIV GLMSO_DINIV, GLMSO.DFINV GLMSO_DFINV, GLMSO.DFINA GLMSO_DFINA, GLMSO.FAB GLMSO_FAB, GLMSO.KTDM GLMSO_KTDM, GLMSO.UPS GLMSO_UPS, GLMSO.UPD GLMSO_UPDfrom GLMSO INNER JOIN GLARA ON GLARA.IDN = GLMSO.IDN AND GLARA.KARA = GLMSO.KARA INNER JOIN GLPSI ON GLPSI.IDN = GLARA.IDN AND GLPSI.KPSI = GLARA.KPSI LEFT JOIN GLTDM ON GLTDM.IDN = GLMSO.IDN AND GLTDM.KTDM = GLMSO.KTDM INNER JOIN GLCMS ON GLCMS.IDN = GLMSO.IDN AND GLCMS.KMSO = GLMSO.KMSO INNER JOIN GLCVA ON GLCMS.IDN = GLCVA.IDN AND GLCMS.KCVA = GLCVA.KCVA INNER JOIN GLCTR ON GLCVA.IDN = GLCTR.IDN AND GLCVA.KCTR = GLCTR.KCTR where GLMSO.IDN = @idnAND GLMSO.DINIV >= @diniv AND GLMSO.DFINA <= @dfina AND GLMSO.FAB = @fab AND ( SELECT COUNT(*) FROM (SELECT DISTINCT GL2.KCTR FROM GLCTA GL2 WHERE GL2.IDN = GLMSO.IDN AND GL2.KARA = GLMSO.KARA AND GL2.KCTR in ('06','07') ) qr) = 2ORDER BY GLMSO.COD[/code]The expected output result should be the row with gmso 0003 which has both of KCTR's 06 Eme3Prodcat2x 07 Eme4Prod Can some one pls help

Denormalizing into a grid

Posted: 12 Jul 2013 02:27 AM PDT

I just read the article on using UNPIVOT to normalize data. I noted that it said that UNPIVOT doesn't exactly do the reverse of PIVOT, and that's too bad. I guess what a need is DEUNPIVOT. I have several ways to do this for a limited set of cases, but I'm hoping for something that can be generalized.ie - given this input[code]DECLARE @OrderDetail TABLE ( orderid INT , personname NVARCHAR(32) , productdate DATE , productname VARCHAR(30) )-- Load Sample DataINSERT INTO @OrderDetailVALUES ( 25815983, 'Jasper', '2013-06-20','Employee Daycare 2 Day' )INSERT INTO @OrderDetailVALUES ( 25815983, 'Jasper', '2013-06-21','Employee Daycare 2 Day' )INSERT INTO @OrderDetailVALUES ( 25815983, 'Jasper', '2013-06-25','Employee Daycare 2 Day' )INSERT INTO @OrderDetailVALUES ( 25815983, 'Jasper', '2013-06-26','Employee Daycare 2 Day' )INSERT INTO @OrderDetailVALUES ( 25815983, 'Jasper', '2013-06-27','Employee Daycare 2 Day' )INSERT INTO @OrderDetailVALUES ( 25815983, 'Jasper', '2013-06-28','Employee Daycare 2 Day' )INSERT INTO @OrderDetailVALUES ( 25815983, 'Milo', '2013-06-20','Employee PreSchool 2 Day' )INSERT INTO @OrderDetailVALUES ( 25815983, 'Milo', '2013-06-21','Employee PreSchool 2 Day' )INSERT INTO @OrderDetailVALUES ( 25815983, 'Milo', '2013-06-24','Employee PreSchool 5 Day' )INSERT INTO @OrderDetailVALUES ( 25815983, 'Milo', '2013-06-25','Employee PreSchool 5 Day' )INSERT INTO @OrderDetailVALUES ( 25815983, 'Milo', '2013-06-26','Employee PreSchool 5 Dayy' )INSERT INTO @OrderDetailVALUES ( 25815983, 'Milo', '2013-06-27','Employee PreSchool 5 Day' )INSERT INTO @OrderDetailVALUES ( 25815983, 'Milo', '2013-06-28','Employee PreSchool 5 Day' )[/code]I need this output[code] Jasper Milo6/20/2013 Employee Daycare 2 Day Employee Preschool 2 Day6/21/2013 Employee Daycare 2 Day Employee Preschool 2 Day6/25/2013 Employee Daycare 4 Day Employee Preschool 5 Day6/26/2013 Employee Daycare 4 Day Employee Preschool 5 Day6/27/2013 Employee Daycare 4 Day Employee Preschool 5 Day6/28/2013 Employee Daycare 4 Day Employee Preschool 5 Day[/code]Such that there is a row for each distinct date, a column for each distinct name and the product name at the intersection of each one. Alternatively, they might ask for distinct names on the rows, products in the columns and dates in the intersections, but I suspect that an solution to one will be extendable to the others.

GetDate() 5 Years ago without time formated 'YYYY-MM-DD'

Posted: 23 Jun 2013 03:40 AM PDT

I'm trying to restrict a query to go back five years ago.I have the following query which is not in the format that I need.[code="plain"]SELECT convert(varchar(11), DateAdd(yy, -5, GetDate()))[/code]Returns:[code="plain"]Jun 23 2008[/code]What I need is the following:[code="plain"]'2008-06-23'[/code]Thank you.

Running Dynamic SQL

Posted: 30 Jun 2013 04:38 AM PDT

Hi All, I have in the past created dynamic SQL in a tally table and looped through to execute it. This is probably not best practice but what alternatives are there, apart from cursors and/or while loops that could be used in this instance?Thanks for your thoughts

No comments:

Post a Comment

Search This Blog