Saturday, September 14, 2013

[SQL Server 2008 issues] How to learn ssis (step-step)

[SQL Server 2008 issues] How to learn ssis (step-step)

How to learn ssis (step-step)

Posted: 09 Jan 2013 11:35 PM PST

Hi, I am totally new to SSIS, please guide me where to start learning SSIS 2008. Is there any e-book where I can get guidance? Please let me know.Thanks

subquery vs join returns are different results

Posted: 13 Sep 2013 12:03 PM PDT

I have 2 tables(label and product) both has the column called uniqueCode which is nvarchar(255) datatype and nullable column. product table has 30,000 records and label table has 5000 records. I would like to get the uniqueCode from label which doesn't exist in the product table upc column. I tried with not in but returns null data but i have 3000 unmatched data that needs to be displayed. Then i tried with join and it is taking 10+ minutes to provide the result.[code="sql"]select * from label where not in(select uniqueCode from Product where uniqueCode is not null) and uniqueCode is not null[/code][code="sql"] SELECT pp.* FROM label pp LEFT JOIN Product p ON pp.uniqueCode <> p.uniqueCode WHERE p.uniqueCode is not NULL[/code][Note : both the columns are non indexed column]Am i missing anything in this query ? why it is taking too much time to execute. Any suggestions....

dont want to Row_Number() in second union start from 1

Posted: 13 Sep 2013 04:11 PM PDT

Hi. Here is my table initialization:[code="sql"]CREATE TABLE #table1(col nvarchar(1));INSERT INTO #table1 SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C';[/code]And here is my sql statement[code="sql"]WITH cte(col,Row_Num) as ( SELECT col, Row_Num= ROW_NUMBER() OVER (ORDER BY(SELECT 0)) FROM cte UNION ALL SELECT col, Row_Num= ROW_NUMBER() OVER (ORDER BY(SELECT 0)) FROM cte)SELECT * FROM CTE[/code]This is output:col,Row_Num-------------a,1b,2c,3a,1b,2c,3Can I change my output to this?col,Row_Num-------------a,1b,2c,3a,4b,5c,6I already tried so many queries, I prevent to write them here to make you bored.And please help me with a general query that solve all same problems, not only a query that solve this particular problem, because my real table are more complex.Thank you very much for help.

SSIS to download .txt file from URL and import data in SQL Server table

Posted: 10 Sep 2013 04:18 AM PDT

There is a text file placed on a URL lets say http://mysubsite.domain.com/Customers.txtThis text file has \t \Column1 \t \Coilumn2 and so on then to have new line data for column1 and then Column2 with n rowsI have to pull this list of customers in existing customer table. if this customer already exists then do nothing if this customer is not in list then add in Customer table. I need some guidance if this could be done through SSIS, if yes then how? I have no idea how to implement this in SSIS. I was trying to write a win32 service in c# to download text file locally and then run BCP to export data in a temp file then call SP to see if the customer is updated then update else if not found then add in Customer table. I am not sure if this could be done through SSIS and if yes i hope it would be fast and quick to implement, if someone already worked on it. What is the best option to choose, I need to know what is the best solution and how it could be done if possible in SSIS? I need to do this on daily bases to get text file up to date with my Customer table.Shamshad Ali

Birthdate

Posted: 13 Sep 2013 02:31 PM PDT

I have this stored proc USE [SMS]GO/****** Object: StoredProcedure [dbo].[usp_Birthday] Script Date: 09/13/2013 22:26:36 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROC [dbo].[usp_Birthday]asbegin-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --catch block --block here if needed begin try begin Transaction;DECLARE @TodaysDate date = getdate(),@PhoneNumber nvarchar(max),@FirstName varchar(25) ,@body nvarchar(MAX),-- = ' Happy Birthday '@txtattnet varchar(25),@Date datetime DECLARE @CurrentMonth int = MONTH(GETDATE()), @CurrentDay int = DAY(GETDATE())DECLARE @Birthdate TABLE(ID int IDENTITY(1,1), PhoneNumber nvarchar(MAX), FirstName varchar(25)) ------check phone carriers -----------set Identity_insert [@Birthday] onINSERT @Birthdate SELECT PhoneNumber, FirstName from dbo.Name where MONTH(BirthDate) = @Date--@CurrentMonth and DAY(BirthDate) = @CurrentDayDECLARE @NumberOfBirthdays smallint = (SELECT COUNT(*) from @Birthdate)DECLARE @MinID int WHILE @NumberOfBirthdays > 0BEGINSET @MinID = (SELECT MIN(ID) From @Birthdate)SET @PhoneNumber = (SELECT PhoneNumber from @Birthdate where ID = @MinID)set @FirstName = (Select FirstName from @Birthdate where ID = @MinID) SET @body = 'Happy BirthDay' + ', ' + @FirstName EXECUTE msdb.dbo.sp_send_dbmail @subject = 'Happy Birthday', @recipients = @PhoneNumber, --@blind_copy_recipients = ***@*****.com; ***@*****.com', @body = @body, @profile_name ='gmail'; DELETE FROM @Birthdate where ID = @MINIDSET @NumberOfBirthdays = @NumberofBirthdays -1ENDThat part work good and it send out the birth day wish to them but I want it to add there age to the message and for some reason I can not figure out how to get that to work.

data search solution

Posted: 13 Sep 2013 01:22 AM PDT

I want to find a value anywhere in a SQL Server Database. what is the solution ?search String = "3496" // exact data searchIt would search [b]all table , all column[/b] for [b]varchar , number[/b] fields etc ....finally output the [b]name of the table and column[/b] where this match is found.Is there any workaround ?

Need help on sqlquery instead of cursor

Posted: 13 Sep 2013 12:37 PM PDT

I have two tables (label,product) and columns are as follows,[b]Product:[/b][code="sql"]p_id (int)(identity),name(nvarchar(100),uniquecode(nvarchar(100)),description(nvarchar(1000)[/code][code="sql"][b]label:[/b]l_id(int)(Identity),name(nvarchar(100),uniquecode(nvarchar(100),p_id(int),description nvarchar(1000)[/code]My requirement is i have to compare label with product based on uniquecode and if data doesn't exits in prodcut than take data from label and insert into product and get the p_id and update into label table based on uniquecode.Currently i am done with the help of fast forward cursor to loop through records from label and insert into product, get the p_id and update into label table(p_id column). Is there any better way to avoid cursor by writing this logic using sql query to improve the performance.also can it be done using Merge statement? If yer please give me sample query

Reloading an exported SS Audit Log, columns are mismatched with labels.

Posted: 13 Sep 2013 09:31 AM PDT

Check out my screencap. I captured some audit records and then exported them to a file. I then reloaded the file in Log File Viewer but now the columns are all one off, and they seem to be misplaced by the "success" value in the second column. I'm not sure what this is, where it comes from, why it isn't in the initial audit log (pre-export) or why it shows up now in the log (post-export). Anyone seen this before? I need these columns to be matched up.

Scripting to connect to Server

Posted: 13 Sep 2013 09:22 AM PDT

I have a 2008 SIS package where I am sending a file to another server.The package runs in development without error, when deployed to the Integration Services Instance it runs fineSchedule the Package to run and try to run it and it errors out saying the destination file does not exist.I'm sure it has something to do with the package and the user name and password for the other server but how do I pass those credentials to the other serverAny help is appreciated.Thanks in Advance.Wayne

Refresh Default Table Value Upon Update

Posted: 13 Sep 2013 04:25 AM PDT

Hi AllI have a column in my table named last_modified_date with a default value of Getdate().I would like the datetime of this column to refresh when I update any of the columns in the table.I see that when I update the description column to fix a typo, for example, that the last_modified_date column remains the same.I am not a big fan of triggers and don't want to circle back and modify my code to update and set the last_modified_date = DEFAULT each time I update records for my various tables I plan to include this column on.Time to ask the audience.... What is the best way to accomplish this?Thank You

Can't Connect to Integration Services

Posted: 13 Sep 2013 04:36 AM PDT

Hi All,I am attempting to set up a user so that he can access SQL Server Integration Services. I gave him access to integration services and I also helped him install SSMS with integration services on his local machine. However, when trying to connect, there is no integration services option, he only sees Database Engine and SQL Server Compact. I am able to connect to integration services on my machine without issue. Any idea why he can't see the integration services option?Thanks,Josh

SQL to list the headers in a table?

Posted: 22 Nov 2011 10:44 AM PST

Does anyone know offhand the SQL to list all the headers in a specified table?Thank you!

Cannot delete trace files

Posted: 13 Sep 2013 02:26 AM PDT

I created some trace files with a SQL Jobs. Now that the jobs have finished & I'm finished with the trace files, I want to delete the files. I get a error stating that another process has the files. I tried restarting SQL Agent, but that didn't help. I tried using XP_delete_files, but the files are not backup files, so that didn't work either. How do I delete the files?

Help with a query

Posted: 13 Sep 2013 03:37 AM PDT

Hello,CREATE TABLE #Tracking ( ID INT IDENTITY(1,1),ProjectName VARCHAR(100),ProductName VARCHAR(20),SNo VARCHAR(600),Haslift BIT,Units INT)INSERT INTO #Tracking ( ProjectName ,ProductName ,SNo ,Haslift,Units)SELECT 'P1','Prod1','P-01',1,183UNIONSELECT 'P1','Prod1','P-01',1,178UNIONSELECT 'P1','ABC','P-01',1,12UNIONSELECT 'P2','Prod1','P-019',1,14UNIONSELECT 'P2','Prod1','P-019',1,1888SELECT * FROM #Tracking DROP TABLE #Tracking For SNo P-01 we have productname =ABC so I want to keep the Haslift field's to 1 for the corresponding ID 's 1,2 and 3For SN0 P-019 we donot have ProductName ABC so I want to update Haslift field to 0 for IDs 4,5Thanks,

Sql Sp not working correctly

Posted: 13 Sep 2013 03:16 AM PDT

I am trying to fins all AD logons that have not logged in for over 45 days, the follwoing is my Query but when it return is returns all records instead of just a few and it should not.ALTER PROCEDURE usp_45Daylate -- Add the parameters for the stored procedure hereASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --Create a temp table to put everyone in Create table #Tempad (sAMAccountname varchar(60), lastlogon datetime) --get all the ad accounts Select sAMAccountName, CASE WHEN CAST(lastLogontimeStamp AS BIGINT) = 0 THEN NULL ELSE CONVERT(varchar(19),(DATEADD(mi,(CAST(lastLogontimeStamp AS BIGINT) / 600000000) + DATEDIFF(Minute,GetUTCDate(),GetDate()),CAST('1/1/1601' AS DATETIME2))), 120) END lastLogontimeStamp From openquery(ADSI,'<LDAP://MY Ladp>;(&(objectClass=User)(objectCategory=Person));sAMAccountName,lastLogontimeStamp;Subtree') INSERT #Tempad (sAMAccountname, lastlogon) select sAMAccountname, lastlogon from #Tempad where datediff(dd, lastlogon, GETDATE()) >= 45ENDGO

tempdb data files - move/initial size

Posted: 09 Sep 2013 07:43 AM PDT

I discovered that we have a couple data files assigned to our tempdb. The primary data file (tempdev.mdf) is on the D: drive but there is a secondary data file (tempdev_2.ndf) that is on C:. The server locked up over the weekend and I suspect that the secondary log file grew to exceed the available hard drive space. My plan is to move the secondary file to the D: drive where there is more space. I see the best practices recommend that the data files be the same size and that we should have one data file per cpu core. Currently the primary data file is set to a initial size of 3GB with autogrowth of 10%. The secondary file is set to initial size of 3MB with autogrowth of 1MB. I think the 3GB is a reasonable size for the tempdb based on the current load but I'm wondering if I should split it up into multiple data files with smaller initial sizes. What should I be checking to determine the number of files?The system is running SS 2008 R2 with 8 cores and 32 GB ram.

Steps to uninstall sql server manually.

Posted: 13 Sep 2013 02:08 AM PDT

Hi All,We have sql server 2005 which need to be uninstalled.we are not able to completely remove from control panel.Can any one let me know steps to do it Manually or some good info to do that...Steps to uninstall sql server manually.?Thanks in advance.

Same query, two users, different performance

Posted: 04 Sep 2013 06:14 AM PDT

Hey Gurus,I have a query that performs differently depending upon the user executing it. This was first brought to my attention because an Excel spreadsheet was taking a long time to load data (Excel 2010, Sql Server 2008 R2). The query was awful, I rewrote it and got better performance. But when the user tried running it from Excel, still had bad performance. After a bit of head scratching I saw that the connection from Excel had a username/password specified, whereas when I ran it from SSMS I was logging in using my username via windows integrated security (and I am dbo). So, I tried a few things:1. In two windows in the same SSMS, run the query simultaneously. One as me, the other using "Execute as Login =".2. Start up two instances of SSMS. Connect in one as me, in the other as the specified login.In any case, when I run the query as me, it takes about 7 1/2 minutes. When I run as the specified user, it takes 12+ minutes (today's measurements. Other days when I run as me I can have it run in as little as 2 minutes, but these times have been consistent today).In any case, I am always executing the exact same statement (SELECT * FROM a table valued function) with the same parameters. The only difference is "who" is logged in.Why would two different user logins have different performance profiles? What should I be looking at to track this down?Thanks in advance.Tom

Looking for Powershell Expert

Posted: 12 Sep 2013 11:41 PM PDT

Hi, i am looking to find powershell expert, so that can help on my query.below script does return sql installed services on server but it will not return anything if service is not installed. my requirement to return wheather service is running then 'running' else 'not installed or not running'. can anyone please help me.# Configuration data.# Add your machine names to check for to the list:[Array] $servers = "server name"; # Defining output format for each column.$fmtName =@{label="Service Name" ;alignment="left" ;width=30 ;Expression={$_.Name};};$fmtMode =@{label="Start Mode" ;alignment="left" ;width=10 ;Expression={$_.StartMode};};$fmtState =@{label="State" ;alignment="left" ;width=10 ;Expression={$_.State};};$fmtStatus =@{label="Status" ;alignment="left" ;width=10 ;Expression={$_.Status};};$fmtMsg =@{label="Message" ;alignment="left" ;width=50 ; ` Expression={ if (($_.StartMode -eq "Auto") -and ($_.State -ne "Running") ) {"Alarm: Stopped"} };};foreach($server in $servers){ $srvc = Get-WmiObject ` -query "SELECT * FROM win32_service WHERE name LIKE '%SQL%' OR name LIKE '%MSOLAP%' OR name LIKE '%ReportServer%' OR name LIKE '%MSDtsServer%'" ` -computername $server ` | Sort-Object -property name; Write-Output ("Server: {0}" -f $server); Write-Output $srvc | Format-Table $fmtName, $fmtMode, $fmtState, $fmtStatus, $fmtMsg; }thanks

sql server service status check for 2000 server

Posted: 12 Sep 2013 10:38 PM PDT

Hi, i am trying to find out service check on 2000 servers but i have got no idea on it. below script runing fine on 2005 and above servers, my requirement is script that will not return any error messages. if anyone fix that will be appreciate. or got any other script that run on any server will be great. Thanks/*---------------------------------------*//* SQL Server Service Check Utility *//*---------------------------------------*/SET NOCOUNT ON/* ------------------------------------------ Inital Setup -----------------------------------------------------*/CREATE TABLE #RegResult( ResultValue NVARCHAR(4))CREATE TABLE #ServicesServiceStatus /*Create temp tables*/( RowID INT IDENTITY(1,1) ,ServerName NVARCHAR(128) ,ServiceName NVARCHAR(128) ,ServiceStatus varchar(128) ,StatusDateTime DATETIME DEFAULT (GETDATE()) ,PhysicalSrverName NVARCHAR(128))DECLARE @ChkInstanceName nvarchar(128) /*Stores SQL Instance Name*/ ,@ChkSrvName nvarchar(128) /*Stores Server Name*/ ,@TrueSrvName nvarchar(128) /*Stores where code name needed */ ,@SQLSrv NVARCHAR(128) /*Stores server name*/ ,@PhysicalSrvName NVARCHAR(128) /*Stores physical name*/ ,@FTS nvarchar(128) /*Stores Full Text Search Service name*/ ,@RS nvarchar(128) /*Stores Reporting Service name*/ ,@SQLAgent NVARCHAR(128) /*Stores SQL Agent Service name*/ ,@OLAP nvarchar(128) /*Stores Analysis Service name*/ ,@REGKEY NVARCHAR(128) /*Stores Registry Key information*/SET @PhysicalSrvName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128)) SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)) SET @ChkInstanceName = @@serverNameIF @ChkSrvName IS NULL /*Detect default or named instance*/ BEGIN SET @TrueSrvName = 'MSQLSERVER' SELECT @OLAP = 'MSSQLServerOLAPService' /*Setting up proper service name*/ SELECT @FTS = 'MSFTESQL' SELECT @RS = 'ReportServer' SELECT @SQLAgent = 'SQLSERVERAGENT' SELECT @SQLSrv = 'MSSQLSERVER' END ELSE BEGIN SET @TrueSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)) SET @SQLSrv = '$'+@ChkSrvName SELECT @OLAP = 'MSOLAP' + @SQLSrv /*Setting up proper service name*/ SELECT @FTS = 'MSFTESQL' + @SQLSrv SELECT @RS = 'ReportServer' + @SQLSrv SELECT @SQLAgent = 'SQLAgent' + @SQLSrv SELECT @SQLSrv = 'MSSQL' + @SQLSrv END /* ---------------------------------- SQL Server Service Section ----------------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLSrvINSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEYIF (SELECT ResultValue FROM #RegResult) = 1 BEGIN INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Sever service*/ EXEC xp_servicecontrol N'QUERYSTATE',@SQLSrv UPDATE #ServicesServiceStatus set ServiceName = 'MS SQL Server Service' where RowID = @@identity UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity TRUNCATE TABLE #RegResultENDELSE BEGIN INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED') UPDATE #ServicesServiceStatus set ServiceName = 'MS SQL Server Service' where RowID = @@identity UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity TRUNCATE TABLE #RegResultEND/* ---------------------------------- SQL Server Agent Service Section -----------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLAgentINSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEYIF (SELECT ResultValue FROM #RegResult) = 1 BEGIN INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Agent service*/ EXEC xp_servicecontrol N'QUERYSTATE',@SQLAgent UPDATE #ServicesServiceStatus set ServiceName = 'SQL Server Agent Service' where RowID = @@identity UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity TRUNCATE TABLE #RegResultENDELSE BEGIN INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED') UPDATE #ServicesServiceStatus set ServiceName = 'SQL Server Agent Service' where RowID = @@identity UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity TRUNCATE TABLE #RegResultEND/* ---------------------------------- SQL Browser Service Section ----------------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\SQLBrowser'INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEYIF (SELECT ResultValue FROM #RegResult) = 1 BEGIN INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Browser Service*/ EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'sqlbrowser' UPDATE #ServicesServiceStatus set ServiceName = 'SQL Browser Service - Instance Independent' where RowID = @@identity UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity TRUNCATE TABLE #RegResultENDELSE BEGIN INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED') UPDATE #ServicesServiceStatus set ServiceName = 'SQL Browser Service - Instance Independent' where RowID = @@identity UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity TRUNCATE TABLE #RegResultEND/* ---------------------------------- Integration Service Section ----------------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\MsDtsServer'INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEYIF (SELECT ResultValue FROM #RegResult) = 1 BEGIN INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Intergration Service*/ EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'MsDtsServer' UPDATE #ServicesServiceStatus set ServiceName = 'Intergration Service - Instance Independent' where RowID = @@identity UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity TRUNCATE TABLE #RegResultENDELSE BEGIN INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED') UPDATE #ServicesServiceStatus set ServiceName = 'Intergration Service - Instance Independent' where RowID = @@identity UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity TRUNCATE TABLE #RegResultEND/* ---------------------------------- Reporting Service Section ------------------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\'+@RSINSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEYIF (SELECT ResultValue FROM #RegResult) = 1 BEGIN INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Reporting service*/ EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@RS UPDATE #ServicesServiceStatus set ServiceName = 'Reporting Service' where RowID = @@identity UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity TRUNCATE TABLE #RegResultENDELSE BEGIN INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED') UPDATE #ServicesServiceStatus set ServiceName = 'Reporting Service' where RowID = @@identity UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity TRUNCATE TABLE #RegResultEND/* ---------------------------------- Analysis Service Section -------------------------------------------------*/IF @ChkSrvName IS NULL /*Detect default or named instance*/ BEGIN SET @OLAP = 'MSSQLServerOLAPService' ENDELSE BEGIN SET @OLAP = 'MSOLAP'+'$'+@ChkSrvName SET @REGKEY = 'System\CurrentControlSet\Services\'+@OLAPENDINSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEYIF (SELECT ResultValue FROM #RegResult) = 1 BEGIN INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Analysis service*/ EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@OLAP UPDATE #ServicesServiceStatus set ServiceName = 'Analysis Services' where RowID = @@identity UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity TRUNCATE TABLE #RegResultENDELSE BEGIN INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED') UPDATE #ServicesServiceStatus set ServiceName = 'Analysis Services' where RowID = @@identity UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity TRUNCATE TABLE #RegResultEND/* ---------------------------------- Full Text Search Service Section -----------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\'+@FTSINSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEYIF (SELECT ResultValue FROM #RegResult) = 1 BEGIN INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Full Text Search service*/ EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@FTS UPDATE #ServicesServiceStatus set ServiceName = 'Full Text Search Service' where RowID = @@identity UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity TRUNCATE TABLE #RegResultENDELSE BEGIN INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED') UPDATE #ServicesServiceStatus set ServiceName = 'Full Text Search Service' where RowID = @@identity UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity TRUNCATE TABLE #RegResultEND/* -------------------------------------------------------------------------------------------------------------*/SELECT PhysicalSrverName AS 'Physical Server Name' /*Display finding*/ ,ServerName AS 'SQL Instance Name' ,ServiceName AS 'SQL Server Services' ,ServiceStatus AS 'Current Service Service Status' ,StatusDateTime AS 'Date/Time Service Status Checked'FROM #ServicesServiceStatus/* -------------------------------------------------------------------------------------------------------------*/DROP TABLE #ServicesServiceStatus /*Perform cleanup*/DROP TABLE #RegResultcheers

SQL Server 2008 R2 Instances WMI Error

Posted: 12 Sep 2013 11:57 PM PDT

Hi there,I have 2 clustered Windows Servers 2012 for SQL Server Instances. I have no shared disks because, I use the cluster with SQL Always on. On each node there is one SQL Server 2008 R2 and two SQL Server 2012 SP1 instances.On my nodes wbemtest.exe could not query in the "root\Microsoft\SqlServer\ComputerManagement10″ Namespace "select * from SqlService where SQLServiceType ='1'". Error: 0×80010105 RPC The Server threw an exeption.This is important for me , because I want to use System Center Operations Manager (SCOM) Monitoring. Without that the SQL 2008 Instance is not discovered by SCOM. When I query against "root\Microsoft\SqlServer\ComputerManagement11″ all instances are visible. So, I think it is an WMI Problem with Windows Server 2012. I had the same error, when I set up my cluster with SQL Server in the SQL Config Manager. http://support.microsoft.com/kb/2849344/de gave me the solution for that. Restart the WMI Service did not solve my problem. Thanks for help.

Allocate Unclassified Payments to Classified Transactions to create Aged Debt Report

Posted: 12 Sep 2013 11:44 PM PDT

I Have a Transaction Table that contains both Debits & Credits with the following structure:DECLARE @Table1 TABLE(Account varchar(40), Service varchar(40), DateCol datetime, amount money)I need to work out Aged Debt. I have used [url=]http://www.sqlservercentral.com/Forums/Topic503770-149-1.aspx#bm503797[/url]to solve most of the issue but I have a twist as follows : • Payments are allocated to oldest balance first. - DONE using SQL from above link• If payments are split by service then they are allocated in those exact amounts to each service. - DONE using SQL from above link• [b]If there is no service description on the payment then the allocation is made proportionally to the outstanding balance per service. - [i]Creating a problem for me.[/i][/b]I need to do a form of allocation of Payments as per [url=]http://www.sqlservercentral.com/Forums/Topic1180765-391-1.aspx[/url]but I am in urgent need of help.There are too many "no service description" payments transactions & I am finding the allocation of these payments challenging. Any assistance will highly appreciated.SQL I am currently using is : [code="sql"]--start cleanup processDECLARE @g DATETIME --just to know how fast this isSET @g=GETDATE();DROP TABLE #cctempDROP TABLE #ccbalDROP TABLE #CCTEMP_originalDROP TABLE ddtempDECLARE @dDateTime DATETIME = '2013-04-30'Use SQL_ExampleDECLARE @Table1 TABLE(Account varchar(40), DocType varchar(40), DateCol datetime, amount money)INSERT INTO @Table1 ( DATEcol,Account,DOCTYPE, Amount) (SELECT [TransDate] ,[AccountNo] ,b.[ServiceDescription] ,sum(CONVERT(money,[Amount])) FROM [SQL_Example].[dbo].[Transactions] a, [SQL_Example].[dbo].[Service Codes] b where a.[Service] = b.[ServiceCode] and CONVERT(money,[Amount])>0 and [TransDate] <= @dDateTime group by [AccountNo],b.[ServiceDescription],[TransDate]) UNION ALL (SELECT [TransDate] ,[AccountNo] ,b.[ServiceDescription] ,sum(CONVERT(money,[Amount])) FROM [SQL_Example].[dbo].[Payments] a, [SQL_Example].[dbo].[Service Codes] b where a.[Service] = b.[ServiceCode] and [TransDate] <= @dDateTime group by [AccountNo],b.[ServiceDescription],[TransDate]) order by [AccountNo],b.[ServiceDescription],[TransDate]--first some test dataCREATE TABLE #CCTEMP_original(memberID varchar(40), ServiceID varchar(40), cashflow money, postdate DATETIME)INSERT #CCTEMP_originalSELECT Account,DocType,amount,DateCol FROM @Table1 order by DATEcol; CREATE CLUSTERED INDEX uci_cctempO ON #CCTEMP_original(memberID,ServiceID,postdate)SELECT memberID, ServiceID, SUM(CASE WHEN cashflow>0 THEN cashflow ELSE 0 END) AS debit, ABS(SUM(CASE WHEN cashflow>0 THEN 0 ELSE cashflow END)) AS credit, SUM(cashflow) AS balINTO #ccbalFROM #CCTEMP_originalGROUP BY memberID,ServiceIDCREATE UNIQUE CLUSTERED INDEX uci_ccb ON #ccbal(memberID,ServiceID)SELECT *, CAST(0 AS money) AS runbal INTO #cctempFROM #CCTEMP_original WHERE EXISTS (SELECT NULL FROM #ccbal WHERE #ccbal.memberid=#CCTEMP_original.memberID AND #ccbal.Serviceid=#CCTEMP_original.ServiceID) CREATE CLUSTERED INDEX uci_cct ON #cctemp(memberid, serviceID, postdate)--create a running grouped total of the debits:DECLARE @prevMemberID varchar(40) SET @prevMemberID=-1;DECLARE @prevServiceID varchar(40) SET @prevServiceID=-1;DECLARE @runbal money SET @runbal =0;DECLARE @dummy money SET @dummy=0; UPDATE #CCTEMPSET @runbal=runbal=CASE WHEN @prevmemberID=memberid AND @prevServiceID=serviceid THEN @runbal ELSE 0 END + cashflow, @dummy=@runbal, @prevmemberID=memberID, @prevServiceID=ServiceIDFROM #CCTEMP WITH (INDEX(uci_cct),tablockX)--create the aging dataSELECT MemberID, ServiceID, SUM(CashFlow) CashFlow, DATEDIFF(DAY,postDate + 1,@dDateTime)/30*30 DaysAged INTO DDtempFROM #cctemp GROUP BY MemberID, ServiceID, DATEDIFF(DAY,postDate + 1,@dDateTime)/30*30 ORDER BY MemberID,ServiceID[/code]

Weird Resource Database Message in Error Log

Posted: 06 Feb 2012 01:06 PM PST

I'm getting this message in the error-log each time on a SQL restart.[quote]The resource database has been detected in two different locations. Attaching the resource database in the same directory as sqlservr.exe at 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCENAME\MSSQL\Binn\sqlservr.exe' instead of the currently attached resource database at 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCENAME\MSSQL\Binn\mssqlsystemresource.mdf'.[/quote]Anyone struck this before and know what it's about? The path is the same for both :blink:
Posted by at

1 comment:

  1. Chris LesnarJune 16, 2021 at 6:37 AM

    Thank you so much for providing information about SSIS and other such aspects of IT which helps in solving many complex IT problems.
    SSIS Upsert

    ReplyDelete

Subscribe to: Post Comments (Atom)

Search This Blog