InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 02:40:19 GMT20http://www.sqlservercentral.com/Forums/Topic1446723-169-1.aspxPlease excuse me if Im not usung the right terminology. I am a SQL Server newbie and have a basic understanding of TSQL. A few chapters into TSQL fundamentals. I am previously a heavy user of Access which is one data source I’m often pointing to in PowerPivot. To achieve desired complex values there are some instances/queries that I have which are split into parts, that is that I may have one query referring to the output of an initial/other query (by name), I think of it as 2-layered, sometimes its “3- layered”. Usually due toaggregate functions and other calculations. I’m not sure what to refer to this as. Issue: I want to sever myself from Access and of query designer and doing things this way, thus im trying to recreate certain Access queries in SSMS. There are straight forward queries and some with joins, but these complex “multi-layered” queries throw me. My question - how is this scenario done in SS? How would a developer achieve this? Is this stored procedure? Lots of joins? I’m a little lost here.Thu, 25 Apr 2013 14:55:35 GMTvega805http://www.sqlservercentral.com/Forums/Topic1414475-169-1.aspxHi, I have a 28 GB SQL 2000 Database that is in need of a re index. I am currently running DBCC DBREINDEX on all tables within My_DB and was wondering if there was a way to tell when the process would complete.Also, should I run DBCC INDEXDEFRAG after DBCC DBREINDEX completes?Fri, 01 Feb 2013 01:15:57 GMTVertigo44http://www.sqlservercentral.com/Forums/Topic1433876-169-1.aspxFolks,I have a cube in Essbase that need to be converted to MSAS - all structure works fine in AS but I have problems to convert Essbase formulas to MDX.This is my Essbase formula:[i]IF (@ISLEV(BCK,0) and @ISLEV(LTV,0) and @ISLEV(MOB,0) and @ISLEV(SCR,0) and @ISLEV("Total Product",0) and @ISLEV("ClassID",0) and @ISLEV("Status",0))C_ENR_BAL+C_BSL_CCF_FNL*C_TOT_UNUSD;ELSE@SUM (@CHILDREN (@CURRMBR (BCK)));@SUM (@CHILDREN (@CURRMBR (LTV)));@SUM (@CHILDREN (@CURRMBR (MOB)));@SUM (@CHILDREN (@CURRMBR (SCR)));@SUM (@CHILDREN (@CURRMBR ("Total Product")));@SUM (@CHILDREN (@CURRMBR ("ClassID")));@SUM (@CHILDREN (@CURRMBR ("Status")));ENDIF;[/i]I´ve tried to convert without success:[i]IIF ([BCK].CURRENTMEMBER.level = 0 and [LTV].level.ordinal = 0 and [MOB].level.ordinal = 0 and [SCR].level.ordinal = 0 and [Total Product].level = 0 and [Class ID].level = 0 and [Status].level = 0, ([Measures].[C ENR BAL]+[Measures].[C BSL CCF FNL])*[Measures].[C TOT UNUSD], SUM({[BCK].children, [LTV].children, [MOB].children, [SCR].children, [Total Product].children, [Class ID].children, [Status].children}) )[/i]Can anyone help me?Thanks in advance,GustavoThu, 21 Mar 2013 09:46:30 GMTsantadehttp://www.sqlservercentral.com/Forums/Topic1427234-169-1.aspxHi I am having trouble trying to write some MDX code.I am trying to get the last measurement result pr. person within a period. I have the following measurements and results:[code="plain"]Person Measurement result Date1 1 overweight 2011-01-011 2 Normal 2011-03-012 3 Underweight 2011-01-012 4 Underweight 2011-02-012 5 Normal 2011-03-011 6 overweight 2012-01-01[/code]Right now I am only able to get a normal count on measurements and distinct count on persons. So I can create a table like this (Full period):[code="plain"] Number of persons Number of measurementsOverweight 1 1Normal 2 3Underweight 1 2[/code] But I would like to have only the last category within my chosen period, so it looks like this (Full period):[code="plain"] Number of persons Number of measurementsOverweight 1 1Normal 1 1Underweight 0 0[/code]But it should also be able to create a table like this for the period (year 2011)[code="plain"] Number of persons Number of measurementsOverweight 0 0Normal 2 2Underweight 0 0[/code]My dimensions and measurements are like the following:[code="plain"][Measures].[Numb persons][Measures].[Numb measurements][Registrationdate].[Date][Person].[ID][result].[Name][/code]I hope someone can help methanks in advance BrJens HWed, 06 Mar 2013 01:42:20 GMTJens Henrikhttp://www.sqlservercentral.com/Forums/Topic1417524-169-1.aspxI have a problem.. I'm trying to install SQL Server 2000 Enterprise Edition on to Windows XP Professional in Cloud hosting, and the installation stops in stage "Starting the server and set your configuration" (probably this, but I not remembered), I waited ca 10 minutes and installation stopped and gave me the log... In the log is the czech words, I'm the translate it![quote] 21:54:09 Begin Setup 21:54:09 8.00.194 21:54:09 Mode = Normal 21:54:09 ModeType = NORMAL 21:54:09 GetDefinitionEx returned: 0, Extended: 0x0 21:54:09 ValueFTS returned: 1 21:54:09 ValuePID returned: 0 21:54:09 ValueLic returned: 0 21:54:09 System: Windows NT WorkStation 21:54:09 SQL Server ProductType: Enterprise Evaluation Edition [0x3] 21:54:09 Begin Action: SetupInitialize 21:54:09 End Action SetupInitialize 21:54:09 Begin Action: SetupInstall 21:54:09 Reading Software\Microsoft\Windows\CurrentVersion\CommonFilesDir ... 21:54:09 CommonFilesDir=C:\Program Files\Common Files 21:54:09 Windows Directory=C:\WINDOWS\ 21:54:09 Program Files=C:\Program Files\ 21:54:09 TEMPDIR=C:\WINDOWS\TEMP\ 21:54:09 Begin Action: SetupInstall 21:54:09 Begin Action: CheckFixedRequirements 21:54:09 Platform ID: 0xf000000 21:54:09 Version: 5.1.2600 21:54:09 File Version - C:\WINDOWS\system32\shdocvw.dll: 6.0.2900.5512 21:54:09 End Action: CheckFixedRequirements 21:54:09 Begin Action: ShowDialogs 21:54:09 Initial Dialog Mask: 0x8300037, Disable Back=0x1 21:54:09 Begin Action ShowDialogsHlpr: 0x1 21:54:09 Begin Action: DialogShowSdWelcome 21:54:10 End Action DialogShowSdWelcome 21:54:10 Dialog 0x1 returned: 1 21:54:10 End Action ShowDialogsHlpr 21:54:10 ShowDialogsGetDialog returned: nCurrent=0x2,index=1 21:54:10 Begin Action ShowDialogsHlpr: 0x2 21:54:10 Begin Action: DialogShowSdMachineName 21:54:11 ShowDlgMachine returned: 1 21:54:11 Name = ZIZKA-59FF41BAF, Type = 0x1 21:54:11 Begin Action: CheckRequirements 21:54:11 Processor Architecture: x86 (Pentium) 21:54:11 Service Pack: 768 21:54:11 ComputerName: ZIZKA-59FF41BAF 21:54:11 User Name: Nuclear 21:54:11 IsAllAccessAllowed returned: 1 21:54:11 OS Language: 0x405 21:54:11 End Action CheckRequirements 21:54:11 This combination of Package and Operating System allows a full product install. 21:54:11 CreateSetupTopology(ZIZKA-59FF41BAF), Handle : 0x14841d0, returned : 0 21:54:11 CreateSetupTopology returned : 0, Handle : 0x14841d0 21:54:11 Topology Type : 1, Return Value : 0 21:54:11 ST_GetPhysicalNode returned : 0, PNHandle : 0x1484220 21:54:11 PN_EnumerateEx returned : 0 21:54:11 PN_GetSQLStates returned : 0, SqlStates : 0x0 21:54:11 PN_StartScan [0x1484220] returned : 0 21:54:11 PN_GetNext [0x1484220] returned : 18, Handle: [0x0] 21:54:11 No more items in enumeration. 21:54:11 ReleaseSetupTopology 21:54:11 Named instance limit: 100, quota: 0 21:54:11 End Action DialogShowSdMachineName 21:54:11 begin ShowDialogsUpdateMask 21:54:11 nFullMask = 0x8300037, nCurrent = 0x2, nDirection = 0 21:54:11 Updated Dialog Mask: 0xbf00037, Disable Back = 0x1 21:54:11 Dialog 0x2 returned: 0 21:54:11 End Action ShowDialogsHlpr 21:54:11 ShowDialogsGetDialog returned: nCurrent=0x4,index=2 21:54:11 Begin Action ShowDialogsHlpr: 0x4 21:54:11 Begin Action: DialogShowSdInstallMode 21:54:12 ShowDlgInstallMode returned: 1 21:54:12 InstallMode : 0x1 21:54:12 End Action DialogShowSdInstallMode 21:54:12 begin ShowDialogsUpdateMask 21:54:12 nFullMask = 0xbf00037, nCurrent = 0x4, nDirection = 1 21:54:12 Updated Dialog Mask: 0xbf40037, Disable Back = 0x1 21:54:12 Dialog 0x4 returned: 1 21:54:12 End Action ShowDialogsHlpr 21:54:12 ShowDialogsGetDialog returned: nCurrent=0x10,index=4 21:54:12 Begin Action ShowDialogsHlpr: 0x10 21:54:12 Begin Action: DialogShowSdRegisterUserEx 21:54:12 End Action DialogShowSdRegisterUserEx 21:54:12 Dialog 0x10 returned: 1 21:54:12 End Action ShowDialogsHlpr 21:54:12 ShowDialogsGetDialog returned: nCurrent=0x20,index=5 21:54:12 Begin Action ShowDialogsHlpr: 0x20 21:54:12 Begin Action: DialogShowSdLicense 21:54:13 End Action DialogShowSdLicense 21:54:13 Dialog 0x20 returned: 1 21:54:13 End Action ShowDialogsHlpr 21:54:13 ShowDialogsGetDialog returned: nCurrent=0x40000,index=18 21:54:13 Begin Action ShowDialogsHlpr: 0x40000 21:54:13 Begin Action: DialogShowSdCliSvr 21:54:13 DisplaySystemPreReq 21:54:15 ShowDlgClientServerSelect returned: 1 21:54:15 Type : 0x2 21:54:15 End Action DialogShowSdCliSvr 21:54:15 begin ShowDialogsUpdateMask 21:54:15 nFullMask = 0xbf40037, nCurrent = 0x40000, nDirection = 1 21:54:15 Updated Dialog Mask: 0xbfc0037, Disable Back = 0x1 21:54:15 Dialog 0x40000 returned: 1 21:54:15 End Action ShowDialogsHlpr 21:54:15 ShowDialogsGetDialog returned: nCurrent=0x80000,index=19 21:54:15 Begin Action ShowDialogsHlpr: 0x80000 21:54:15 Begin Action: DialogShowSdInstanceName 21:54:15 Begin Action: ShowDlgInstanceName 21:54:17 End Action: ShowDlgInstanceName 21:54:17 ShowDlgInstanceName returned : 1 21:54:17 InstanceName : MSSQLSERVER 21:54:17 CreateSetupTopology(ZIZKA-59FF41BAF), Handle : 0x14841d0, returned : 0 21:54:17 CreateSetupTopology returned : 0, Handle : 0x14841d0 21:54:17 Topology Type : 1, Return Value : 0 21:54:17 ST_GetPhysicalNode returned : 0, PNHandle : 0x1484220 21:54:17 PN_EnumerateEx returned : 0 21:54:17 PN_GetSQLStates returned : 0, SqlStates : 0x0 21:54:17 PN_StartScan [0x1484220] returned : 0 21:54:17 PN_GetNext [0x1484220] returned : 18, Handle: [0x0] 21:54:17 No more items in enumeration. 21:54:17 ReleaseSetupTopology 21:54:17 End Action DialogShowSdInstanceName 21:54:17 begin ShowDialogsUpdateMask 21:54:17 nFullMask = 0xbfc0037, nCurrent = 0x80000, nDirection = 1 21:54:17 Updated Dialog Mask: 0xbfc0037, Disable Back = 0x1 21:54:17 Dialog 0x80000 returned: 1 21:54:17 End Action ShowDialogsHlpr 21:54:17 ShowDialogsGetDialog returned: nCurrent=0x100000,index=20 21:54:17 Begin Action ShowDialogsHlpr: 0x100000 21:54:17 Begin Action: DialogShowSdSetupType 21:54:17 Begin Action: Setup Type 21:54:19 SQL program folder: C:\Program Files\Microsoft SQL Server 21:54:19 SQL data folder: C:\Program Files\Microsoft SQL Server 21:54:19 Windows system folder: C:\WINDOWS\system32\ 21:54:19 Prog req: 38073, Data req: 34432, Sys req: 158694 21:54:19 Prog avail: 967915440, Data avail: 967915440, Sys avail: 967915440 21:54:19 Prog req vs. avail, 231199, 967915440 21:54:19 Data req vs. avail, 34432, 967915440 21:54:19 Sys req vs. avail, 193126, 967915440 21:54:19 DisplaySystemPreReq 21:54:19 [SetupTypeSQL] 21:54:19 szDir = C:\Program Files\Microsoft SQL Server 21:54:19 szDir = %PROGRAMFILES%\Microsoft SQL Server 21:54:19 Result = 301 21:54:19 szDataDir = C:\Program Files\Microsoft SQL Server 21:54:19 szDataDir = %PROGRAMFILES%\Microsoft SQL Server 21:54:19 End Action: Setup Type 21:54:19 Setup Type: Typical (301) 21:54:19 End Action DialogShowSdSetupType 21:54:19 begin ShowDialogsUpdateMask 21:54:19 nFullMask = 0xbfc0037, nCurrent = 0x100000, nDirection = 301 21:54:19 Updated Dialog Mask: 0xbdc0037, Disable Back = 0x1 21:54:19 Dialog 0x100000 returned: 301 21:54:19 End Action ShowDialogsHlpr 21:54:19 ShowDialogsGetDialog returned: nCurrent=0x400000,index=22 21:54:19 Begin Action ShowDialogsHlpr: 0x400000 21:54:19 Begin Action: DlgServices 21:54:21 ShowDlgServices returned: 1 21:54:21 [DlgServices] 21:54:21 Local-Domain = 3855 21:54:21 AutoStart = 15 21:54:21 Result = 1 21:54:21 End Action DlgServices 21:54:21 begin ShowDialogsUpdateMask 21:54:21 nFullMask = 0xbdc0037, nCurrent = 0x400000, nDirection = 1 21:54:21 Updated Dialog Mask: 0xbdc0037, Disable Back = 0x1 21:54:21 Dialog 0x400000 returned: 1 21:54:21 End Action ShowDialogsHlpr 21:54:21 ShowDialogsGetDialog returned: nCurrent=0x800000,index=23 21:54:21 Begin Action ShowDialogsHlpr: 0x800000 21:54:21 Begin Action: DlgSQLSecurity 21:54:22 ShowDlgSQLSecurity returned: 1 21:54:22 LoginMode = 1,szPwd 21:54:22 End Action DlgSQLSecurity 21:54:22 begin ShowDialogsUpdateMask 21:54:22 nFullMask = 0xbdc0037, nCurrent = 0x800000, nDirection = 1 21:54:22 Updated Dialog Mask: 0xbdc0037, Disable Back = 0x1 21:54:22 Dialog 0x800000 returned: 1 21:54:22 End Action ShowDialogsHlpr 21:54:22 ShowDialogsGetDialog returned: nCurrent=0x1000000,index=24 21:54:22 Begin Action ShowDialogsHlpr: 0x1000000 21:54:22 Begin Action: DlgCollation 21:54:22 ShowDlgCollation returned: 1 21:54:22 collation_name = Czech_CI_AS,locale_name = Czech,lcid = 0x405,SortId = 0,dwCompFlags = 0x30001 21:54:22 End Action DlgCollation 21:54:22 begin ShowDialogsUpdateMask 21:54:22 nFullMask = 0xbdc0037, nCurrent = 0x1000000, nDirection = 1 21:54:22 Updated Dialog Mask: 0xbdc0037, Disable Back = 0x1 21:54:22 Dialog 0x1000000 returned: 1 21:54:22 End Action ShowDialogsHlpr 21:54:22 ShowDialogsGetDialog returned: nCurrent=0x2000000,index=25 21:54:22 Begin Action ShowDialogsHlpr: 0x2000000 21:54:22 Begin Action: DlgNetwork 21:54:22 ShowDlgNetwork returned: 1 21:54:22 [DlgServerNetwork] 21:54:22 NetworkLibs = 255 21:54:22 TCPPort = 1433 21:54:22 TCPPrxy = Default 21:54:22 NMPPipeName = \\.\pipe\sql\query 21:54:22 Result = 1 21:54:22 End Action DlgNetwork 21:54:22 begin ShowDialogsUpdateMask 21:54:22 nFullMask = 0xbdc0037, nCurrent = 0x2000000, nDirection = 1 21:54:22 Updated Dialog Mask: 0xbdc0037, Disable Back = 0x1 21:54:22 Dialog 0x2000000 returned: 1 21:54:22 End Action ShowDialogsHlpr 21:54:22 ShowDialogsGetDialog returned: nCurrent=0x8000000,index=27 21:54:22 Begin Action ShowDialogsHlpr: 0x8000000 21:54:22 Begin Action: DialogShowSdStartCopy 21:54:22 End Action DialogShowSdStartCopy 21:54:22 begin ShowDialogsUpdateMask 21:54:22 nFullMask = 0xbdc0037, nCurrent = 0x8000000, nDirection = 1 21:54:22 Updated Dialog Mask: 0xbdc0037, Disable Back = 0x1 21:54:22 Dialog 0x8000000 returned: 1 21:54:22 End Action ShowDialogsHlpr 21:54:22 ShowDialogsGetDialog returned: nCurrent=0x0,index=0 21:54:22 End Action ShowDialogs 21:54:22 Begin Action ProcessBeforeDataMove: 21:54:22 DeinstallStart returned (C:\Program Files\Microsoft SQL Server\MSSQL): 0 21:54:22 End Action ProcessBeforeDataMove: 21:54:22 Begin Action SetToolsComponentSelection: 21:54:22 End Action SetToolsComponentSelection: 21:54:22 Begin Action ProcessComponentSelection: 21:54:22 End Action ProcessComponentSelection 21:54:22 Begin Action LogSelectedComponents: 21:54:22 SQLProg 21:54:22 SQLProg\SQLServr 21:54:22 SQLProg\SQLServr\Help 21:54:22 SQLProg\SQLServr\SCMDev 21:54:22 SQLProg\SQLServr\SCMDev\SCMh 21:54:22 SQLProg\SQLServr\SCMDev\SCMX86Lb 21:54:22 SQLProg\SQLServr\SCMDev\SCMALb 21:54:22 SQLProg\SQLServr\Rs1033 21:54:22 SQLProg\SQLServr\RsIntl 21:54:22 SQLProg\SQLServr\ActiveX 21:54:22 SQLProg\SQLServr\System 21:54:22 SQLProg\UpgTools 21:54:22 SQLProg\UpgTools\UpgSys 21:54:22 SQLProg\UpgTools\ActiveX 21:54:22 SQLProg\UpgTools\Res1033 21:54:22 SQLProg\UpgTools\ResOther 21:54:22 SQLProg\UpgTools\Resld 21:54:22 SQLProg\ReplSupp 21:54:22 SQLProg\ReplSupp\ReplDat 21:54:22 SQLProg\ReplSupp\RepComm 21:54:22 SQLProg\ReplSupp\RepNoDk 21:54:22 SQLProg\ReplSupp\ActiveX 21:54:22 SQLProg\Install 21:54:22 SQLProg\System 21:54:22 SQLProg\SvrExt 21:54:22 SQLProg\SvrExt\Help 21:54:22 SQLProg\SvrExt\SvrExtRs 21:54:22 SQLProg\SvrExt\ResIntl 21:54:22 SQLProg\Dat 21:54:22 SQLProg\DatSmpl 21:54:22 SQLProg\BaseSys 21:54:22 SQLProg\BaseBinn 21:54:22 SQLProg\BaseInst 21:54:22 SQLProg\Symbols 21:54:22 SQLProg\Symbols\EXE 21:54:22 SQLProg\Symbols\DLL 21:54:22 SQLProg\Perfmon 21:54:22 SQLProg\Perfmon\System 21:54:22 SQLProg\Root 21:54:22 MgtTool 21:54:22 MgtTool\SEM 21:54:22 MgtTool\SEM\HTML 21:54:22 MgtTool\SEM\MSD98 21:54:22 MgtTool\SEM\MSD98SYS 21:54:22 MgtTool\SEM\MSD98Res 21:54:22 MgtTool\SEM\MSD98Hlp 21:54:23 MgtTool\SEM\Help 21:54:23 MgtTool\SEM\Res1033 21:54:23 MgtTool\SEM\ResIntl 21:54:23 MgtTool\SEM\MSD98RsI 21:54:23 MgtTool\SEM\ActiveX 21:54:23 MgtTool\SEM\ActiveX\Res1033 21:54:23 MgtTool\SEM\ActiveX\ResIntl 21:54:23 MgtTool\SEM\Scripts 21:54:23 MgtTool\SEM\OLEDB 21:54:23 MgtTool\SEM\OLEDB\Res1033 21:54:23 MgtTool\SEM\OLEDB\ResIntl 21:54:23 MgtTool\Profiler 21:54:23 MgtTool\Profiler\Help 21:54:23 MgtTool\Profiler\Res1033 21:54:23 MgtTool\Profiler\ResIntl 21:54:23 MgtTool\Qryanlz 21:54:23 MgtTool\Qryanlz\Help 21:54:23 MgtTool\Qryanlz\Res1033 21:54:23 MgtTool\Qryanlz\ResIntl 21:54:23 MgtTool\DTCCLi 21:54:23 MgtTool\Wzcnflct 21:54:23 MgtTool\Wzcnflct\WzcnHlp 21:54:23 MgtTool\Wzcnflct\Wzcn1033 21:54:23 MgtTool\Wzcnflct\WzcnOthr 21:54:23 MgtTool\Wzcnflct\WzcnCmn 21:54:23 MgtTool\UtilSys 21:54:23 MgtTool\UtilBinn 21:54:23 Connect 21:54:23 Connect\ConnSys 21:54:23 Books 21:54:23 Books\Bookso 21:54:23 Books\Bookso\Utils 21:54:23 DevTools 21:54:23 DevTools\Dbg Int 21:54:23 DevTools\Dbg Int\Dbg Int Common 21:54:23 DevTools\Dbg Int\EXE 21:54:23 CoreRepl 21:54:23 CoreRepl\Res1033 21:54:23 CoreRepl\ResIntl 21:54:23 Core 21:54:23 Core\Res1033 21:54:23 Core\ResOther 21:54:23 Repostry 21:54:23 Repostry\RepstSys 21:54:23 Repostry\Res1033 21:54:23 Repostry\ResIntl 21:54:23 CoreMisc 21:54:23 CoreMisc\ActiveX 21:54:23 CoreMisc\ActiveX\Res1033 21:54:23 CoreMisc\ActiveX\ResIntl 21:54:23 CoreMisc\Res1033 21:54:23 Monarch 21:54:23 Monarch\Monr1033 21:54:23 Monarch\MonrIntl 21:54:23 Jet 21:54:23 CoreInst 21:54:23 CoreCOM 21:54:23 CoreCOM\Res1033 21:54:23 CoreCOM\ResIntl 21:54:23 CoreTool 21:54:23 CoreTool\Res1033 21:54:23 CoreTool\ResOther 21:54:23 DBLibCli 21:54:23 SFExt 21:54:23 SFExt\ActiveX 21:54:23 SFExt\ActiveX\Res1033 21:54:23 SFExt\ActiveX\ResIntl 21:54:23 SFExt\Res1033 21:54:23 SFExt\ResIntl 21:54:23 Trace 21:54:23 Trace\Res1033 21:54:23 Trace\ResOther 21:54:23 MiscCore 21:54:23 MC 21:54:23 MC\MC1033 21:54:23 MC\MCIntl 21:54:23 MC\Help 21:54:23 SQLMgr 21:54:23 SQLMgr\Res1033 21:54:23 SQLMgr\ResIntl 21:54:23 SvrTool 21:54:23 SvrTool\Res1033 21:54:23 SvrTool\ResIntl 21:54:23 DTSUI 21:54:23 DTSUI\Res1033 21:54:23 DTSUI\ResIntl 21:54:23 MSOlap 21:54:23 MSOlap\Res1033 21:54:23 MSOlap\ResIntl 21:54:23 ATL 21:54:23 ATL\winnt 21:54:23 ATL\win9x 21:54:23 MFC42U 21:54:23 VC 21:54:23 VB 21:54:23 OCX1 21:54:23 SQLAdHlp 21:54:23 SQLAdHlp\Res1033 21:54:23 SQLAdHlp\ResOther 21:54:23 {E07FDDBE-5A21-11d2-9DAD-00C04F79D434} 21:54:23 {E07FDDC7-5A21-11d2-9DAD-00C04F79D434} 21:54:23 {E07FDDC0-5A21-11d2-9DAD-00C04F79D434} 21:54:23 {E07FDDBF-5A21-11d2-9DAD-00C04F79D434} 21:54:23 End Action LogSelectedComponents 21:54:23 Begin Action InstallPkgs: 21:54:23 Begin Action: Locked Connectivity Files Check 21:54:23 ConnectivityLocked returned: 0 21:54:23 Operation has been suceffull ended. //Translated 21:54:23 End Action: Locked Connectivity Files Check 21:54:23 Setup is installing Microsoft Data Access Components (MDAC) ... 21:54:23 C:\SQLEVAL\x86\Other\sqlredis.exe /q:a /C:"setupre.exe WARN=1 -s -SMS" 21:54:30 ExitCode: 0 21:54:30 Setup is installing Microsoft Distributed Transaction Coordinator (MSDTC) ... 21:54:30 C:\WINDOWS\TEMP\SqlSetup\Bin\cldtcstp.exe -SupportDir "C:\WINDOWS\TEMP\SqlSetup\Bin" -DTCPkg "C:\SQLEVAL\x86\Other\dtcsetup.exe" -LogFile "C:\WINDOWS\sqlstp.log" 21:54:30 Process Exit Code: (0) 21:54:30 /Q:A /T:C:\WINDOWS\TEMP\ixp001.tmp 21:54:30 Setup is installing HTML Help ... 21:54:30 HTML Help installer exit code: 0 21:54:51 End Action InstallPkgs 21:54:51 Begin Action MoveFileData: 21:54:51 Enabled SELFREGISTERBATCH 21:54:51 Enabled CORECOMPONENTHANDLING 21:55:01 Begin Action: MoveFileDataSpecial 21:55:01 End Action: MoveFileDataSpecial 21:55:01 End Action MoveFileData 21:55:01 Begin Action ProcessAfterDataMove: 21:55:01 C:\SQLEVAL\x86\Binn\hhcol.exe C:\Program Files\Microsoft SQL Server\80\Tools\Books 21:55:01 End Action ProcessAfterDataMove 21:55:01 Begin Action BuildServer: 21:55:01 C:\WINDOWS\TEMP\SqlSetup\Bin\scm.exe -Silent 1 -Action 5 -ExePath "C:\Program Files\Microsoft SQL Server\MSSQL\binn\sqlservr.exe" -Service "MSSQLSERVER" 21:55:02 Process Exit Code: (0) 21:55:02 Begin Action: CreateRegistrySetSQL 21:55:02 End Action: CreateRegistrySetSQL 21:55:02 Begin Action: RegWriteSetupEntry 21:55:02 End Action: RegWriteSetupEntry 21:55:02 Begin Action: CreateSer 21:55:02 End Action: CreateSer 21:55:02 Begin Action: SkuIt 21:55:02 End Action: SkuIt 21:55:02 SetFileSecuritySQLAndAdmin for C:\Program Files\Microsoft SQL Server\MSSQL returned: 0, 0 21:55:02 SetRegSecuritySQLAndAdmin for Software\Microsoft\MSSQLServer\MSSQLServer returned: 0, 0 21:55:02 Begin Action: UpdateSystemPath 21:55:02 Path successfully updated. 21:55:02 %SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\Microsoft SQL Server\80\Tools\BINN 21:55:02 End Action: UpdateSystemPath 21:55:03 C:\Program Files\Microsoft SQL Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I MSSQLSERVER -V 1 -M 0 -Q "Czech_CI_AS" -H 3211902 -U sa -P ############################################################################### Starting Service ... Czech_CI_AS -m -Q -T4022 -T3659 Connecting to Server ... driver={sql server};server=ZIZKA-59FF41BAF;UID=sa;PWD=;database=master [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server not exists or access denied. //Translated [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()). driver={sql server};server=ZIZKA-59FF41BAF;UID=sa;PWD=;database=master [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server not exists or access denied. //Translated [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()). driver={sql server};server=ZIZKA-59FF41BAF;UID=sa;PWD=;database=master [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server not exists or access denied. //Translated [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()). SQL Server configuration failed. ############################################################################### 21:57:03 Process Exit Code: (-1) 22:56:35 Setup failed to configure the server. Refer to the server error logs and C:\WINDOWS\sqlstp.log for more information. 22:56:35 Action CleanUpInstall: 22:56:35 C:\WINDOWS\TEMP\SqlSetup\Bin\scm.exe -Silent 1 -Action 4 -Service SQLSERVERAGENT 22:56:35 Process Exit Code: (1060) Selected service not installed. // Translated 22:56:35 C:\WINDOWS\TEMP\SqlSetup\Bin\scm.exe -Silent 1 -Action 4 -Service MSSQLSERVER 22:56:55 Action Exit Handler: 22:56:58 Process Exit Code: (0) 22:56:58 StatsGenerate returned: 2 22:56:58 StatsGenerate (0x0,0x1,0xf000000,0x300,1033,303,0x0,0x1,0,0,0 22:56:58 StatsGenerate -1,Nuclear) 22:56:58 Installation Failed.[/quote]I've checked in the google stock in stage "installing service...." and I found if I have all NTFS permissions... Have administration permissions, but dont know NTFS permissions... What's the problem?Fri, 08 Feb 2013 02:04:34 GMTgamelasterhttp://www.sqlservercentral.com/Forums/Topic1414927-169-1.aspxTable 1A B C D FA1 B1 NULL NULL NULLA2 B2 NULL NULL NULLA3 B3 NULL NULL NULLA4 B4 NULL NULL NULLA5 B5 NULL NULL NULLTable 2Loc NameA1 WhiteA2 BlackA3 RedA4 OrangeA5 BlueB1 GreenB2 YellowB3 GoldB4 RoseB5 SilverSat, 02 Feb 2013 02:45:58 GMTramanathan.raman88http://www.sqlservercentral.com/Forums/Topic1414384-169-1.aspxi try to convert from access database to MSSQL Server but there's an error the error msg is "Attached table is not updatable because it has no unique index." can any one explain what is this error. do you have any sugestion or another way to convert it in sql.. thanks in advanceThu, 31 Jan 2013 15:54:57 GMTjohn_edman24http://www.sqlservercentral.com/Forums/Topic1413985-169-1.aspxI am Ramanathan and working in SQL SERVER 2008….Please help meTable1Location--FieldA1A2A3B1B2B3How to change an below format from table1A B--FieldA1 B1A2 B2A3 B3Thu, 31 Jan 2013 04:22:04 GMTramanathan.raman88http://www.sqlservercentral.com/Forums/Topic405714-169-1.aspxHi, Can anyone get back to me with tips regarding this.I am gettign connection pool error while using the .Net application. When i checked the Activity monitor on sql server 2005, i found that many process ID where presenet with status sleeping. When i check the details for these process ID, i found the SQL query in that. I then checked the applciation which executes the query..and found the connection is closing as well as doing a dispose() even on the connectin object. Then why does the activity monitor show so many process ID with sleeping status...??What does sleeping mean..???Any link i can refer to...??How to kill these process all togetther..??Now i restart when ever this prblem occurs..!!! or mnauly kill each process Looking forward to your suggestions..thanks...Tue, 02 Oct 2007 09:18:19 GMTbytezonehttp://www.sqlservercentral.com/Forums/Topic780795-169-1.aspxI have a measure circ that should roll up along some dimensions but there are threedimensions where I do not want it rolling up to parent levels. When viewing the parent I want to see the same value that is at the leaf level. I am creating a newcalculated member in my cube to hold the correct value. This MDX code works for one level:iif(isleaf([Enr Mo].CURRENTMEMBER) ,[Measures].[Circ],[Enr Mo].CURRENTMEMBER.LastChild)The other dimensions are ATCC which has two levels and [proj horizon] also with two levels.I am having trouble getting this to work for more than one dimension. I cannotnest IIF statements and I can't get CASE WHEN statements to work either. I am using SQL Server Analysis Services Version 8 which I think is SQL Server 2000. I think this sort ofthing should be easy in OLAP but I'm having trouble getting started. Can anyone offer a suggestion? Thanks in Advance!Regards,BrianTue, 01 Sep 2009 09:02:46 GMTBRIAN.ZWILLINGhttp://www.sqlservercentral.com/Forums/Topic1409641-169-1.aspxOur core wait state is MSQL_XP.The documented cause is:"Caused when a task is waiting for an extended stored procedure to end. SQL Server uses this wait state to detect potential MARS application deadlocks. The wait ends when the extended stored procedure call completes."How do I find this extended stored procedure?Setup a trace for what?Here are our top ten wait states.I have no bench mark to know what this means really.Our system shows 1200 to 1400 ms wait time most often.Description, Wait, Total WaitSQLTRACE_INCREMENTAL_FLUSH_SLEEP, 1068.926441, 6024.2158MSQL_XP, 0, 250.6122539PREEMPTIVE_OS_GETPROCADDRESS, 0, 250.6047996ASYNC_NETWORK_IO, 0.333300003, 12.60383067PAGEIOLATCH_EX , 1.099890011, 8.90908973PAGEIOLATCH_SH, 0.499950005, 4.126295243WRITELOG, 0.166650002, 2.165293104PREEMPTIVE_OS_AUTHENTICATIONOPS, 0.166650002, 1.326802214BACKUPTHREAD, 0, 0.927363158BACKUPIO, 0, 0.725151497Any clarification on the meaning and impact of these wait states would be greatly appreciated.I've read so much about this I feel like I know less than when I started.I know what causes them but don't know what to do or even if this level of wait state is a problem. We use Idera SQL diagnostic manager to monitor this.What we see is slow disk reads and writes for the data drive (D) (9ms per write) and memory paging.We moved temp DB off of D to a solid state drive. But it didn't help at all.I have a screen shot of the Idera Monitor I can send to anyone interested in helping me solve this performance issue.tia,ToddMon, 21 Jan 2013 10:25:28 GMTTC-416047http://www.sqlservercentral.com/Forums/Topic690526-169-1.aspxI want to create a random string each time a new record is ceated in the database. I have the SQL code which will create the string but I am not sure how to make it execute and insert the value each time a new record is created.Sat, 04 Apr 2009 17:15:47 GMTAFIFMhttp://www.sqlservercentral.com/Forums/Topic1405043-169-1.aspxI'm having a hard time with this query this afternoon. I have 3 tables cash, revenue, expense. I need to join them together keeping all rows along with begin_balance, end_balance, and revenue. There are several rows per building all having different totals. Sample data:[b]Revenue_table[/b] building_code - 125, 125, 125 function_code – null, null, null fund_code - 001, 001, 001 receipt_code – 14, 18, 31object_code - null, null, nullrevenue - 24463.24, 0, 825625.16begin_balance - null, null, nullend_balance - null, null, nullyear - 2007-12[b]Expense_table[/b] building_code - 125, 125, 125 fund_code - 001, 001, 001 function_code – 11, 11, 11 receipt_code - null, null, nullobject_code – 41, 41, 42 revenue - 24463.24, 0, 825625.16begin_balance - null, null, nullend_balance - null, null, nullyear - 2007-12[b]Cash_table[/b] building_code - 125, 125, 125 function_code - null, null, null fund_code - 001, 006, 007 receipt_code - null, null, nullobject_code - 41, 41, 42 revenue - null, null, null begin_balance - 206891.77, 0, 424.65end_balance - 764207, -13522.95, 494.65year - 2007-12What is needed is to join on building_code and foreign keys to merge data together than possibly union the tables together creating one table in which all the data exists. I was uncertain as to how to join on tables with many to many relationships without producing unnecessary matches.Wed, 09 Jan 2013 15:19:47 GMTwtolberthttp://www.sqlservercentral.com/Forums/Topic1400994-169-1.aspxIm a business/financial/data analyst who wishes to make a transition to a BI solutions development/BI information delivery role specializing in the MS platform (with a good general foundation of SQL Server). I feel that I have a strong functional knowledge of the platform , but I wish to have much more of technical knowledge which I'm working towards starting with TSQL, DAX and some MDX - bouncing around. But there's so much more with respect to dev solutions; programmability (visual studio), C#, .NET, ADO - I know I'm a newbie and that this is a journey but not having the lineage in IT im at times insecure and could use help on the most practical (technical) path to hit the ground running in say a (functional) consulting role for example. So to the forum and veterans, [b]I ask the following in hopes to refine my professional development plan in the context of ramping up my knowledge base.[/b]• a new SharePoint 2013 environment has been implemented at work. I'm ramping up my SP knowledge via PluralSight, but from a BI reporting perspective and to get comfortable with SSRS, AS and self service, [b]what can I do at home to practice designing and deploying to SharePoint (and a server). [/b]I appreciate the new features of 2013 but I want to learn how to (manually) deploy BI solutions as if I was using 2007 or 2010 that are less BI integrated. Would you recommend me setting up foundation on a home machine?• same question but with respect to deploying a db to a server; [b]what's a good way to practice at home in a server environment? Is there a source for a novice like me on the ABC's of setting up a test environment? [/b]Remember, Ive worked as an analyst and I understand how to in theory but not reality. • General development curiosity. This may sounds funny, but I don't "see" how I would build a solution using SQL Server similar to how I would in Access which I use a lot of. Im NOT comparing the two, I mean for example how would I build the form to a SS db? How could I "connect" it to the db? Where am I deploying it to? Resource?• in a DBA framework, what are the most important concepts I should familiarize myself with considering the direction I want to go and to have a strong "tool belt"Amongst all the reading and study these are some things I don't "see" yet. Anyway, Ill stop there. There are very seasoned IT people here and seeing my background perhaps you can understand where I am coming from and why I ask some of this questions.Fri, 28 Dec 2012 11:33:48 GMTvega805http://www.sqlservercentral.com/Forums/Topic1400767-169-1.aspxHi guys,Looking for some assistance with an MS CRM environment.Basically there is a stored procedure on the SQL Sever called Aplan.getpendersThe C# code which is run as a job on another server has the following code which I assume is calling the above stored procedure. var sql = String.IsNullOrEmpty(_p1) ? String.Format("exec GetPenders") : String.Format("exec GetPenders '{0}'", _p1);The rest of the code goes on to generate a letter with the information gathered by the sproc.The problem I am having is that there is something I need to change in the letter that is specified in the Sproc pending certain conditions but no matter what I do it does not seem to reflect the changes in the generated email.What I have done:1. Right click sproc, modify, made the changes, parse and execute - when i click on modify again I can see that the changes have been saved - this does not resolve the issue.2. I rebuilt the C# code on the other server - (just trying everything, not sure if its necessary)Because the code runs on a different server to the SQL server, I am guessing that the sproc needs to be copied over. The problem is I cannot find the location of the sproc on the SQL server and I cannot find a copy of it on the other server either. Correct me if I am wrong but I need to be looking for a file called GetPenders??? But is it an exe file or an sql file?I apologise if that sounds confusing but I have very basic SQL knowledge and less again in C# programming so its all a bit of a maze to me..... Any help or suggestions greately appreciated!!ThanksAngeThu, 27 Dec 2012 18:27:01 GMTangelinemarrenhttp://www.sqlservercentral.com/Forums/Topic1399696-169-1.aspxHello,I have swapped the names for multiple tables using sp_rename.The SPs and views which were associated with it are working fine but the reference to the objects is still pointing to the renamed object.Is there any of fixing the reference in bulk without dropping and recreating the associated objects?Thanks in Advance for your help.Sat, 22 Dec 2012 11:33:06 GMTKnowledge Hunterhttp://www.sqlservercentral.com/Forums/Topic1398545-169-1.aspxMSSQL2000In a Windows Authentication mode instance, if <domain>\<username> (say abc\def) has been been disabled on the Active Directory, does it matter that the same abc\def still exists as SYSADMIN on the instance level security logins?For that matter, will it matter if it was in a mixed mode instance?I don't think it matters because the authentication is deriving username / password from Active Directory. But, am I wrong?Wed, 19 Dec 2012 10:32:11 GMTMSSQL_NOOBhttp://www.sqlservercentral.com/Forums/Topic1396194-169-1.aspxMSSQL Server A: MSSQL2000 - set to Windows Authentication modeMSSQL Server B: MSSQL2000 - set to SQL Server & Windows Authentication mode.My client machine: running MS SSMS2008My domain login: SysAdmin privileges on both machines.Linked Servers connection from Server A to Server BWhen I issue commandSELECT * FROM SERVERB.DBNAME.DBO.TABLENAMEI get the following error:Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.Why is that? Is it because Server A is only set to Windows Authentication mode??Thu, 13 Dec 2012 07:42:30 GMTMSSQL_NOOBhttp://www.sqlservercentral.com/Forums/Topic1395512-169-1.aspxHi,I want to do MCSA exam. Do any dump available for this? Please let me know.Thanks,AnandhWed, 12 Dec 2012 02:11:29 GMTsanandh87http://www.sqlservercentral.com/Forums/Topic471017-169-1.aspxHi there, I was wondering if somebody could tell me how to configure the notification alerts on a job properties. Basically I want to receive an email when a job is completed or is failed.ThanksTue, 18 Mar 2008 10:52:07 GMTClaudia-343424http://www.sqlservercentral.com/Forums/Topic1388340-169-1.aspxI am about as much of a newbie as possible. I've been working with Report Builder 3.0 for a week now. We have a piece of software built on SQL 2008R2. I want to build a custom report that will auto-populate from certain tables. The problem: I want the report to list data from only the active session. The only way I see to differentiate between sessions is that in the SessionActivity dataset, the "SessionEnded" will be blank. My thought was an expression either to hide all rows not null in that column or show only rows that are null in that column. I've been unable to successfully stumble through on my own. Any help would be greatly appreciated.Sat, 24 Nov 2012 15:17:12 GMTtfink717http://www.sqlservercentral.com/Forums/Topic455740-169-1.aspxHi, I need to create 2 indexes on a large table(~200 million records). I wanted to lock the entire tableduring these 2 transactions to prevent any update or insertto the table by the users, but the user should be able to do select. I'm on SQL Server 2005. My example of code below. Could you please let me know if I have it correctly. use testdbgobegin tran select top 1 * table1 with(tablockx) create nonclustered index (IX_ID_Type_Date) on table1(id,type, date)if @@error <> 0 rollback tranelse commit trangobegin tran select top 1 * table1 with(tablockx) create nonclustered index (IX_ID_Company) on table1(id, Company)if @@error <> 0 rollback tranelse commit trangoThu, 14 Feb 2008 08:30:34 GMTsqlbluehttp://www.sqlservercentral.com/Forums/Topic1370148-169-1.aspxI am fairly new to SQL and pressed for time. I don't think this is difficult to do but I can't seem to work it out myself.I have two tables in a one-to-many relationship:Table1 contains records in which each key is unique and not duplicated. There are two fields: table1_key and table1_data.Table2 contains records with multiple duplicated keys which are also in table1. There are also two fields: table2_key and table2_data. The data here, for example, are record keys for a third table. The same key can occur many times, even hundreds as can the same data, but there are no records in which the key and data are both identical.For each unique record key in table1, I would like to return not only the data field from table1 but the count of the number of occurances of that record's key found in table2. I'm pretty sure this is possible, just not sure of the syntax.Hope someone can help.ThanksMon, 08 Oct 2012 21:56:35 GMTdean-hodgsonhttp://www.sqlservercentral.com/Forums/Topic1362523-169-1.aspxdeclare @asd varchar(20) set @asd ='asdaaaadffa'Fri, 21 Sep 2012 04:12:45 GMTsaltpepohttp://www.sqlservercentral.com/Forums/Topic1365109-169-1.aspxHello, I have a database problem since yesterday. --------------------------------------------------------------------------------------------------------------------------------------------------The error is:-----------------------------------------------------MessageThe log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.Cannot close event log because there are still event handle users active.MessageError: 9001, Severity: 21, State: 1.-----------------------------------------------------What I do:----------------------------------------------------- exec('ALTER DATABASE [' + @DBName + '] SET OFFLINE WITH ROLLBACK IMMEDIATE') print '*** Taken ' + @DBName + ' offline .' exec('ALTER DATABASE [' + @DBName + '] SET ONLINE ') print '*** Brought ' + @DBName + ' online .' declare @logName varchar(100) select @logname=name from master.dbo.sysaltfiles where groupid=0 and dbid=(select dbid from master.dbo.sysdatabases where name=@DBName) exec ('USE [' + @DBName + '] DBCC checkdb with ALL_ERRORMSGS ,EXTENDED_LOGICAL_CHECKS,NO_INFOMSGS ') print '*** Checked ' + @DBName-----------------------------------------------------We have in this moment:-----------------------------------------------------2300 databases on the specific SQL Instance. The version is SQL SERVER 2008 R2 SP2.-----------------------------------------------------Please HELP!!! I' don't know what to do.Thank you very much.Thu, 27 Sep 2012 03:05:46 GMTdtraianhttp://www.sqlservercentral.com/Forums/Topic1332892-169-1.aspxHi All,I have two tables for example,tblCategories(CategoryID[INT],CategoryName[VARCHAR])tblEmployees(EmployeeID[INT],EmployeeName[VARCHAR],CategoryIDs[VARCHAR])Example OUTPUT Like,tblCategories:CategoryID CategoryName1 AAA2 BBB3 CCCtblEmployees:EmployeeID EmployeeName CategoryIDs1 XXX 1,32 YYY 2,33 ZZZ 1,2I want to join both the tables & i want to get the resultis it possible?if it is possible how to join the tablePlease give me the query& also result for me.Thanks In Advance,Venkatesh.Fri, 20 Jul 2012 05:46:18 GMTvenkidesaikhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxHi all Is there any best way to get month name in SQL SERVER 2005.I know that we can do this using our own function, but just i want to conform that if SQL SERVER provide any function for month name if we have month number i.e. 1,2..12ThanksFri, 08 Feb 2008 00:23:40 GMTShakti Singh Dulawathttp://www.sqlservercentral.com/Forums/Topic1351985-169-1.aspxHere's the Union that I clearly can't do.... I have 30 tables of inspections, deliniated by *Inspection. I have included the (of course erroring) Select * from a few below. Most columns are identical and this is what I'm traying to make a report of. Is there a way to grab all current tables in the db with columns like '%Inspection' and get all of the unique columns too? Any help would be GREATLY appreciated! [code="sql"]SELECT 'Beach' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Source, DateCreated, DocumentID, Name, FacilityType, AllYearRound, BillingType, EHO, InspectionDate, Type, ComplaintDate, OtherType, ScheduledInspectionDate, Billable, BillablePermit, NextInspDue, NextManualDue, FollowupInspectionRequired, NextInspection, Comments, ViolationModule, EHSID, NumCritical, NumNonCritical, NumRepeat, TotHazardRating, TotalViolations, Enforcement, ReleaseDate, SectionDisplay, SectionModule, SectionNumber, SectionGroup, SectionCategory, SectionDescription, SectionStatusFROM BeachInspectionUNION ALLSELECT 'BodyArt' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Source, DateCreated, DocumentID, Name, FacilityType, AllYearRound, BillingType, EHO, InspectionDate, Type, ComplaintDate, OtherType, ScheduledInspectionDate, Billable, BillablePermit, NextInspDue, NextManualDue, FollowupInspectionRequired, NextInspection, NumCritical, NumNonCritical, NumRepeat, TotHazardRating, Enforcement, ReleaseDate, Comments, ReviewedBy, ReviewedDate, TimeType, TimeIn, TimeOut, Min, Hours, TimeSpent, Module, TrackingComments, LotusDateCreated, LotusDateModifiedFROM BodyArtInspectionUNION ALLSELECT 'Campground' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Source, DateCreated, DocumentID, Name, FacilityType, AllYearRound, BillingType, EHO, InspectionDate, Type, ComplaintDate, OtherType, ScheduledInspectionDate, Billable, BillablePermit, NextInspDue, NextManualDue, FollowupInspectionRequired, NextInspection, NumCritical, NumNonCritical, NumRepeat, TotHazardRating, Enforcement, ReleaseDate, Comments, ReviewedBy, ReviewedDate, TimeType, TimeIn, TimeOut, Min, Hours, TimeSpent, Module, TrackingComments, LotusDateCreated, LotusDateModifiedFROM CampgroundInspectionUNION ALLSELECT 'ChildCare' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Source, DateCreated, DocumentID, Name, FacilityType, AllYearRound, BillingType, EHO, InspectionDate, Type, ComplaintDate, OtherType, ScheduledInspectionDate, Billable, BillablePermit, NextInspDue, NextManualDue, FollowupInspectionRequired, NextInspection, NumCritical, NumNonCritical, NumRepeat, TotHazardRating, ReviewedBy, ReviewDate, Comments, TimeType, TimeIn, TimeOut, Min, Hours, TimeSpent, Module, TrackingComments, LotusDateCreated, LotusDateModifiedFROM ChildCareInspectionUNION ALLSELECT 'Food' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Source, DateCreated, DocumentID, Name, FacilityType, AllYearRound, BillingType, EHO, InspectionDate, Type, ComplaintDate, OtherType, ScheduledInspectionDate, Billable, BillablePermit, SampleSelection, Samples, Records, TotalMachines, TotalInspected, NextInspDue, NextManualDue, FollowupInspectionRequired, NextInspection, ObservedSeating, Comments, CriticalControlComments, ViolationModule, ViolationSubModule, EHSID, NumCritical, NumNonCritical, NumRepeat, TotHazardRating, TotalViolationsFROM FoodInspectionUNION ALLSELECT 'General' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Source, DateCreated, DocumentID, Name, FacilityType, AllYearRound, BillingType, EHO, InspectionDate, Type, ComplaintDate, OtherType, ScheduledInspectionDate, Billable, BillablePermit, NextInspDue, NextManualDue, FollowupInspectionRequired, NextInspection, NumCritical, NumNonCritical, NumRepeat, TotHazardRating, Enforcement, ReleaseDate, Comments, ReviewedBy, ReviewedDate, TimeType, TimeIn, TimeOut, Min, Hours, TimeSpent, Module, TrackingCommentsFROM GeneralInspectionUNION ALLSELECT 'Hotel' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Source, DateCreated, DocumentID, Name, FacilityType, AllYearRound, BillingType, EHO, InspectionDate, Type, ComplaintDate, OtherType, ScheduledInspectionDate, Billable, BillablePermit, NextInspDue, NextManualDue, FollowupInspectionRequired, NextInspection, NumCritical, NumNonCritical, NumRepeat, TotHazardRating, Enforcement, ReleaseDate, Comments, ReviewedBy, ReviewedDate, TimeType, TimeIn, TimeOut, Min, Hours, TimeSpent, Module, TrackingComments, LotusDateCreated, LotusDateModifiedFROM HotelInspectionUNION ALLSELECT 'LaborCamp' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Source, DateCreated, DocumentID, Name, FacilityType, AllYearRound, BillingType, EHO, InspectionDate, Type, ComplaintDate, OtherType, ScheduledInspectionDate, Billable, BillablePermit, NextInspDue, NextManualDue, FollowupInspectionRequired, NextInspection, NumCritical, NumNonCritical, NumRepeat, TotHazardRating, Enforcement, ReleaseDate, Comments, ReviewedBy, ReviewedDate, TimeType, TimeIn, TimeOut, Min, Hours, TimeSpent, Module, TrackingComments, LotusDateCreated, LotusDateModifiedFROM LaborCampInspectionUNION ALLSELECT 'MobileHome' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Source, DateCreated, DocumentID, Name, FacilityType, AllYearRound, BillingType, EHO, InspectionDate, Type, ComplaintDate, OtherType, ScheduledInspectionDate, Billable, BillablePermit, NextInspDue, NextManualDue, FollowupInspectionRequired, NextInspection, NumCritical, NumNonCritical, NumRepeat, TotHazardRating, ReviewedBy, ReviewedDate, Comments, TimeType, TimeIn, TimeOut, Min, Hours, TimeSpent, Module, TrackingComments, LotusDateCreated, LotusDateModifiedFROM MobileHomeInspectionUNION ALLSELECT 'Pool' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Source, DateCreated, DocumentID, Name, FacilityType, AllYearRound, BillingType, EHO, InspectionDate, Type, ComplaintDate, OtherType, ScheduledInspectionDate, Billable, BillablePermit, NextInspDue, NextManualDue, FollowupInspectionRequired, NextInspection, Regulations, NumCritical, NumNonCritical, NumRepeat, TotHazardRating, MainChlorine, CombinedChlorine, Alkalinity, MainpH, MainTemp, CyanuricAcid, MainPressure, MainFlow, Enforcement, ReleaseDate, Comments, ReviewedBy, ReviewedDate, TimeType, TimeIn, TimeOut, Min, Hours, TimeSpent, Module, TrackingComments, LotusDateCreated, LotusDateModifiedFROM PoolInspectionUNION ALLSELECT 'School' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Name, FacilityType, InspectionType, AllYearRound, BillingType, EHO, InspectionDate, ComplaintDate, FollowupInspectionRequired, NextInspection, Action, ReleaseDate, ObservedSeating, NumCritical, NumNonCritical, NumRepeat, Billable, DocumentID, EHSID, NextInspDue, ScheduledInspectionDate, Source, ViolationDisplay, ViolationSection, ViolationSectionDescription, ViolationSubModule, ViolCode, ViolCorrectedSet, ViolCorrectiveActions, ViolDescription, ViolObservations, ViolRepeat, ViolSection, TotalTimeSpentHoursFROM SchoolInspectionUNION ALLSELECT 'SepticRemoval' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Source, DateCreated, DocumentID, Name, FacilityType, AllYearRound, BillingType, EHO, InspectionDate, Type, ComplaintDate, OtherType, ScheduledInspectionDate, Billable, BillablePermit, FacilityPhysicalSuite, FacilityPhysicalBuilding, FacilityPhysicalDirection, FacilityPhysicalStreetName, FacilityPhysicalStreetType, FacilityPhysicalStreetSuffix, FacilityPhysicalCity, FacilityPhysicalProvince, FacilityPhysicalPostalCode, FacilityTelephone, FacilityApprovedNumber, FacilityRegType, OwnersName, OwnersAddress, OwnersCity, OwnersState, OwnersPostalCode, EHOPhoneNumber, SubOffice, SubOfficeAddress, SubOfficeCity, SubOfficePostalCode, SubOfficePhone, NextInspDue, NextManualDue, CreatedFromInspID, DisplayFollowUp, FollowupInspectionRequired, NextInspection, Comments, ViolationModule, EHSID, NumCritical, NumNonCritical, NumRepeat, TotHazardRating, LetterGrade, TotalViolations, HazRatingType, SectionModule, SectionNumber, SectionGroup, SectionCategory, SectionDescription, SectionStatusFROM SepticRemovalInspectionUNION ALLSELECT 'SolidWaste' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Source, DateCreated, DocumentID, FacilityName, FacilityType, EHO, LogDate, TimeIn, TimeOut, InspectionStatsHist, TimeHoursHist, TimeSpent, NextInspDue, NextManualDue, FollowupInspectionRequired, NextInspection, NumCritical, NumNonCritical, NumRepeat, ViolationDisplay, CorrectionDisplay, ViolCode, ViolCorrectedSet, ViolCorrectiveActions, ViolCriticalSet, ViolDescription, ViolObservations, ViolRepeat, ViolSection, Type, LicenseStatusAction, ReleaseDate, ParentID, InspectionDate, TotHazardRatingFROM SolidWasteInspectionUNION ALLSELECT 'SummerCamp' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Source, DateCreated, DocumentID, Name, FacilityType, AllYearRound, BillingType, EHO, InspectionDate, Type, ComplaintDate, OtherType, ScheduledInspectionDate, Billable, BillablePermit, NextInspDue, NextManualDue, FollowupInspectionRequired, NextInspection, NumCritical, NumNonCritical, NumRepeat, TotHazardRating, Enforcement, ReleaseDate, Comments, ReviewedBy, ReviewedDate, TimeType, TimeIn, TimeOut, Min, Hours, TimeSpent, Module, TrackingComments, LotusDateCreated, LotusDateModifiedFROM SummerCampInspectionUNION ALLSELECT 'Water' AS module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Source, DateCreated, DocumentID, Name, FacilityType, AllYearRound, BillingType, EHO, InspectionDate, Type, ComplaintDate, OtherType, ScheduledInspectionDate, Billable, BillablePermit, NextInspDue, NextManualDue, FollowupInspectionRequired, NextInspection, Regulations, NumCritical, NumNonCritical, NumRepeat, TotHazardRating, IssuePermit, IssuePermitComments, DeficienciesCorrected, DeficienciesCorrectedComments, CommentsChemSample, ChemSampleComments, ActionTaken, ActionComments, InformationExchanged, InformationExchangedComments, Followup, FollowupComments, IssueBoilAdvisory, IssueBoilAdvisoryComments, WrittenOrder, WrittenOrderComments, MicroSample, MicroSampleComments, LiftBoilAdvisory, LiftBoilAdvisoryComments, TimeType, TimeIn, TimeOut, Min, Hours, TimeSpent, Module, TrackingComments, Comments, LotusDateCreated, LotusDateModifiedFROM WaterInspection[/code]Wed, 29 Aug 2012 20:47:21 GMTkinderdesignhttp://www.sqlservercentral.com/Forums/Topic1351640-169-1.aspxHi,I installed SQL Server 2012 on Windows Server 2008 R2. And I installed Enterprise Edition. After Configuring Reporting Services Configuration Manager I clicked on the URL and I got the following error in Reports Log File. appdomainmanager!ReportManager_0-1!1294!08/29/2012-09:55:13:: i INFO: Appdomain:9 ReportManager_MS2012_0-1-129907221069185388 unloadinglibrary!DefaultDomain!1294!08/29/2012-09:55:13:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerAppDomainManagerException: Failed to create Report Server HTTP Runtime, Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerAppDomainManagerException: An error occurred when attempting to start the application domain ReportManager within the Report Server service. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerHttpRuntimeInternalException: An internal or system error occurred in the HTTP Runtime object for application domain ReportManager_MS2012_0. ---> System.Web.HttpException: Could not load file or assembly 'Oracle.Web, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file specified. (C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config line 176) ---> System.Configuration.ConfigurationErrorsException: Could not load file or assembly 'Oracle.Web, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file specified. (C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config line 176) ---> System.IO.FileNotFoundException: Could not load file or assembly 'Oracle.Web, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file specified. --- End of inner exception stack trace ---;rshost!rshost!1294!08/29/2012-09:55:13:: e ERROR: Failed to create HttpRuntime 80131500.rshost!rshost!1294!08/29/2012-09:55:13:: e ERROR: Failed to get appdomain 80131500, pipeline=0x00000001F80A7410.rshost!rshost!1294!08/29/2012-09:55:13:: e ERROR: Error state. Internal abort for pipeline=0x00000001F80A7410 ...appdomainmanager!DefaultDomain!1030!08/29/2012-09:55:18:: i INFO: Appdomain:9 ReportManager_MS2012_0-1-129907221069185388 unregistered.rshost!rshost!1030!08/29/2012-09:55:18:: i INFO: Application domain type ReportManager statistics: created: 1, unloaded: 1, failed: 0, timed out: 0.Wed, 29 Aug 2012 08:05:33 GMTScott1336http://www.sqlservercentral.com/Forums/Topic563476-169-1.aspxI like to run the .bat file every 1/2 hr via SSIS package. Can you please help me how to write SSIS package for running .bat file? ThanksWed, 03 Sep 2008 16:48:12 GMTpremtghttp://www.sqlservercentral.com/Forums/Topic1342922-169-1.aspxHello,I am new to SQL and have been asked to perform a query of our Dispatch database. Although I am able to pull most of the information I need I am having trouble with the following.The following queries work seperately on their own:SELECT unit_status_datetime AS UnitLeftSceneDateTimeFROM unit_status_histWHERE cad_command_code = 'EH'SELECT unit_status_datetime AS UnitLeftSceneDateTimeFROM unit_status_histWHERE cad_command_code = 'AH'Problem is I need to include these in a query that will be selecting multiple columns along with these.Is there a way to specify multiple aliases for a single column a retrive the data I need?Thank you for your time!Chris AbbottThu, 09 Aug 2012 12:19:16 GMTdirtyshorellchttp://www.sqlservercentral.com/Forums/Topic1301620-169-1.aspxI wrote some scripts for correcting my old sql 2000 db schema compared to new sql 2000 db. This is done through a loop operations. First check if any table not found in my old db, then running a table creation script for creating that table. If any system stored procedures or scripts availble for table creation ?. I want table scripts like when we take 'generate sql script' process.Thu, 17 May 2012 04:23:33 GMTssa2010http://www.sqlservercentral.com/Forums/Topic1334115-169-1.aspxI have a 22gb database with a column called [var]. I need to change this column from decimal(12,5) to decimal(22,7). The database must stay on-line and is collecting data 24/7. My overall free hard drive space is less than 20gb. Looking for some help!Mon, 23 Jul 2012 16:02:49 GMTgbloom-998285http://www.sqlservercentral.com/Forums/Topic608101-169-1.aspxHi,Is there a way to setup email notification to be sent whenever SQL server (or server agent) restarts? This is basically for monitoring server crashes/restarts.Thanks in advance.Cheers!Vinay.Mon, 24 Nov 2008 23:46:09 GMTvinayachandran_vhttp://www.sqlservercentral.com/Forums/Topic1326178-169-1.aspxHi,Iwould like to have the delimited values in one of the columns in sql server table to be split into three rows.ex: Project id Teams Impacted 5032401 BSS;NOC;Operations;Engineering;Network;Cableas below having 3 * 8 blocksProject id : Team1 Team2 team 3 Team4 team5 team6 team 7 team 85032401 BSS Engineering NOC Network Operations Cablewe have to ingore null values as well.Any help regarding this is greatly appreciated. Please suggest at the earliest.Thanks and Regards,AruFri, 06 Jul 2012 09:36:41 GMTarubala75http://www.sqlservercentral.com/Forums/Topic1321173-169-1.aspxHi Friends,I am trying to call a function (which returns month value when passing date as parameter ) in another query but it is resulting into [u]Syntax error converting datetime from character string.[/u]select (CASE WHEN (sht.mode='SI' Or sht.mode='AI') THEN (y3.[M_NAD_IND]) ELSE (y1.[M_NAD_IND]) end)as [Maxdate_Nad_IND], max(b.shpdate) as [MAX_shpDate], count(shipment)as Shp_Count into #maxDate_swiftfrom allstat b Left Join nad_new y1 on y1.adrno=b.Shipper Left Join nad_new y3 on y3.adrno=b.consignee left join shipmenttype sht on b.[type]=sht.[type]where b.shpdate between (DATEADD(m, -6, @year + '-' + + '-01'))and (DATEADD(dd, -1, @year + '-' + 'select * from mon_allstat('+ b.shpdate + ')' + '-01'))group by (CASE WHEN (sht.mode='SI' Or sht.mode='AI') THEN (y3.[M_NAD_IND]) ELSE (y1.[M_NAD_IND]) end)--@monthGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOPls. help :(Tue, 26 Jun 2012 06:08:27 GMTNeetu Sharmahttp://www.sqlservercentral.com/Forums/Topic258305-169-1.aspxHi,I am trying to send mail from SQL Server 2000 but when the email is delivered, the subject line is empty.I have looked through the forums here and found someone with the same problem which they fixed it by reverting to a previous version of Outlook. Unfortunately, I'm not in a position to be able to do that.I've also entered: sql server xp_sendmail subject into Google, and gone through the first 22 pages of results, but to no avail.Here is what I have done:I have a machine nwb-xtest-beta, running Windows XP Pro, with all OS updates, and logged in as "xtest".I am running SQL Server 2000, with SP2.I have installed Outlook 2003, installed all the updates available to it, configured a profile ("XTest") and sent and received email fine.I configured the MSSQLSERVER service to be started as user corpdom\xtest, and restarted the service.I went to Enterprise manager, Support Services, SQL Mail, and selected XTest from the list of profiles.I then went to Query Analyzer, and ran:EXEC xp_sendmail 'rupertw', @subject="test subject", @message="test message"The message was delivered, but with an empty subject line. There was no subject in the SMTP headers (i.e. there was nothing after Subject: ).I wondered if it was a problem which had been fixed with a later service pack, so I went to my machine - nwb-rupertw (so I could be sure that it was the application of the SP which had fixed it), running the same software, but configured everything as user "rupertw".Executing the same SQL statement worked, including a good subject line.I went back to nwb-xtest-beta and reconfigured everything using the "rupertw" id. Again, it all worked, including good subject line.No errors reported in event viewer.It would imply that there is a difference in how xp_sendmail sees the different users, tho I've been told that they're the same.Has anyone got a suggested solution to this problem?Many thanksRgdsRupertTue, 14 Feb 2006 10:18:00 GMTRupert Woodmanhttp://www.sqlservercentral.com/Forums/Topic1317764-169-1.aspxI want to retrieve data from another DB to my warehouse using SSIS.Is it possible for the automatic retrieval and update my warehouse when data changes?Tue, 19 Jun 2012 02:03:16 GMTsimplekarthihttp://www.sqlservercentral.com/Forums/Topic1313591-169-1.aspxHi, I want to open Primavera P6, but the system asks me for configuration, when I started the following massage came:Select an existing database alias, or enter a new database alias name to be created. If you are configuring a connection for the first time, select your database driver type using the list of driver types below. If you are not sure which alias or driver type to use, contact your administrator.Database alias:PMDBDriver type:Microsoft SQL serverAnyone tell me please which one should I select. I am new in configuration of database.Thank youZarmina PopalSun, 10 Jun 2012 09:24:29 GMTz_popalhttp://www.sqlservercentral.com/Forums/Topic1302733-169-1.aspxHi Guys,I have to write a script to update ALL date fields in the database my company's software uses. This is mainly an exercise to help me learn SQL (I am encouraged to use all resources). Fortunately, most of the date fields are in datetime, but I am having a hard time figuring out what to do with the varchar/bigint/nvarchar ones such as 2004-06-23, 1276449969378, May 20 2, 06/14/2011. I have to update all fields one year ahead, but leave the rest of the date unchanged. Can anyone please point me in the right direction of where to start?Thanks :)Fri, 18 May 2012 11:53:35 GMTnic79