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

---

• How to learn ssis (step-step)
• subquery vs join returns are different results
• dont want to Row_Number() in second union start from 1
• SSIS to download .txt file from URL and import data in SQL Server table
• Birthdate
• data search solution
• Need help on sqlquery instead of cursor
• Reloading an exported SS Audit Log, columns are mismatched with labels.
• Scripting to connect to Server
• Refresh Default Table Value Upon Update
• Can't Connect to Integration Services
• SQL to list the headers in a table?
• Cannot delete trace files
• Help with a query
• Sql Sp not working correctly
• tempdb data files - move/initial size
• Steps to uninstall sql server manually.
• Same query, two users, different performance
• Looking for Powershell Expert
• sql server service status check for 2000 server
• SQL Server 2008 R2 Instances WMI Error
• Allocate Unclassified Payments to Classified Transactions to create Aged Debt Report
• Weird Resource Database Message in Error Log

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:

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 - General / SQL Server 2008 To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[how to] Lock Pages in Memory keep SQL database engine stuck at allocating 60MB ram?

---

• Lock Pages in Memory keep SQL database engine stuck at allocating 60MB ram?
• What do you call numerical coding systems that are left-aligned?
• Is this compound index unnecessary?
• SQL Server: Additional cpus slow down batch
• Easily, quickly replace MySQL table?
• Table Design for multiple user define assignment
• How to config MySQL Enterprise Cluster
• What do you call a relationship between two entities without a foreign key?
• retrieving data speed tweaks MS SQL 2005
• Converting .TPS (TopSpeed) to SQL
• MySQL: sysbench test - InnoDB vs Memory tables
• SQL Query to fetch data from 4 different tables [migrated]
• On Oracle 12c, permitting and disallowing crashrecovery
• Import Oracle schema data without losing modifications of stored procedures
• Query getting periodically stuck in 'copying to tmp table' state, never completes
• Accumulo table design methodology
• Disaster Recovery for PostgreSQL 9.0
• Oracle Patch Update
• Replicated Database Log File Maintenance
• SQL Server Designers, Failed Saves, and Generated Scripts
• How to avoid empty rows in SSIS Excel Destination?
• How to add rows/columns to the table in runtime in SSRS 2008
• How to disable oracle's MAX_ENABLED_ROLES limit
• In MySQL, does the order of the columns in a WHERE clause affect query performance,why?
• Delete word, its meanings, its meaning's example sentences from DB
• MySQL concurrent INSERTs
• How can I optimize this query and support multiple SKUs?
• How to modify an update in Oracle so it performs faster?
• Query to find and replace text in all tables and fields of a mysql db

Lock Pages in Memory keep SQL database engine stuck at allocating 60MB ram? Posted: 13 Sep 2013 08:17 PM PDT I'm using Window Server 2003 Enterprise. Microsoft SQL Server 2005 Standard Edition. The problem is the SQL server memory always stuck at allocating 1.7GB ram max even i have 11GB ram left. According to this thread, which the thread starter have them same issues: http://www.sqlservercentral.com/Forums/Topic499500-146-1.aspx#bm499829 . I tried adding /APE to my boot.ini, add the network service account which is running sqlserv.exe to Lock Pages in Memory option. And reconfigure SQL use AWE to allocate memory. But i have no lucky, after restart the server. The SQL database engine cannot allocating more than 60MB ram. Which is terrible failure as my expected. So after that, i must restore the Lock Pages in Memory setting - remove the network service account from Lock Pages in Memory option, restart my server and it come back to the first problem. The SQL server database engine come back stuck at allocating 1.7GB ram. So the Lock Pages in Memory keep SQL database engine stuck at allocating 60MB ram ? And how to resolve the first problem now ?

What do you call numerical coding systems that are left-aligned? Posted: 13 Sep 2013 08:28 PM PDT I'm writing an importing mechanism for the NAICS database. I have a few questions about this code-format. I've seen it before and I like it the setup. I'm going to ask some other questions about best-practices and navigation of this data, and I'd like to simply refer to it by the right name. Essentially this is an example of the data CODE, TITLE "21","Mining, Quarrying, and Oil and Gas Extraction" "212","Mining (except Oil and Gas)" "2121","Coal Mining" "21211","Coal Mining" "212111","Bituminous Coal and Lignite Surface Mining " "212112","Bituminous Coal Underground Mining " So if Bituminous Coal Underground Mining was your organization type, your code would be 212112. You could look up to find you were in the business-genre of Coal Mining, and up again to find you were in the business of Mining (except Oil and Gas), and up again to find you were in the business of "Mining, Quarrying, and Oil and Gas Extraction". What is such a scheme called, is there a term to refer to this kind of organization of data? I want to call something like recursive-base10 or recursive-decimal is there a name of it though?

Is this compound index unnecessary? Posted: 13 Sep 2013 03:57 PM PDT I have a large table which contains sensor data, along with the fields: sensor_id timestamp I do queries against it using these fields almost exclusively. There are multiple sensors, and different sensors might have the same timestamp for a given set of data, so neither index can be unique. I created three indexes: one for each of these columns (not unique), and a compound one for both (unique). The table is constantly being written to, and queries to read data seem increasingly slow. My question is, is the compound index unnecessary? Would it be faster to have only the two separate indexes? (Or remove those and keep only the compound index?) No other columns are used for filtering query data. My question is similar to this one: Do I need separate indexes for each type of query, or will one multi-column index work?

SQL Server: Additional cpus slow down batch Posted: 13 Sep 2013 02:48 PM PDT I'm running SQL Server 2012 in Windows 2008R2 in a virtualized environment. I've observed the following under both VMware Workstation 9 and Hyper-V 2012R1 and I don't know how to address it. I've got a batch that takes around 5 minutes to run when there is a single CPU in the virtual machine. Bumping up anywhere from 2-8 causes it to take over 10 minutes to run. Watching the Task Manager I see that there is not much if any parallel execution and lots of context switching. If I limit sqlservr.exe to a single CPU by setting the processor affinity in Task Manager the time drops back down to 5 minutes. The particular batch that I'm running is makes heavy use of cursors and dynamic sql which cannot be eliminated. The query has been profiled and optimized. Statistics are all up to date and indexes are rebuilt. Is there anything I can do to SQL Server to get better behavior? This seems not right. I would like to add additional CPU resources to the VM so that they can be used if necessary without a drastic performance hit for serialized processing. CPU is i7-4770K with VT-x enabled both with and without hyperthreading enabled.

Easily, quickly replace MySQL table? Posted: 13 Sep 2013 06:02 PM PDT We have a process which will regenerate a table from scratch, drop the original table, and move the new table into the original's place. Now this should happen very quickly, but sometimes a website request hits the table after it's been dropped but before the new one is renamed. Apart from coding the website to be more robust when there's a database error, is there an easier way to do this?

Table Design for multiple user define assignment Posted: 13 Sep 2013 12:21 PM PDT I have a Weather table which contains the weather information (precipitation, humidity, temperature, etc.) of nearly the last two years. New weather condition info is inserted into the table every 15 minutes using information from a local weather observation station. Now users from a web application can define their own weather type definition (meaning that they can define that "Hot" corresponds to when the temperature is above 26 centigrade ect.) I currently have three table: Weather (Table contains weather info updated every 15 mins) WeatherID | ObservationTime | Temperature | Pressure | .... WeatherGroup (Table contains user defined information) WeatherGroupID | GroupName | IgnoreTemp | TempHigh | TempLow | IgnoreHumidity | et... 1 | Snowy | 0 | 0 | -100 | 1 | ... WeatherAssignment (Table contains the mapping between a weather record and the weather group WeatherAssignmentID | WeatherID | WeatherGroupID 1 | 23 | 2 1 | 35 | 2 ... The issue is that pulling data every 15 minutes means that one year's of data contains 35,040 records. And the more users create their own weather definitions the WeatherAssignment table will keep growing larger. Now for the next four, five years I don't see this design causing any problems. But for those of you with database design experience is there a better way I store the data so the data?

How to config MySQL Enterprise Cluster Posted: 13 Sep 2013 11:38 AM PDT I have 2 mysql enterprise server but I want to make it clustering by active and standby. How to config this? Thank you.

What do you call a relationship between two entities without a foreign key? Posted: 13 Sep 2013 09:54 AM PDT Let's say I have two tables that are related in that one table contains a field that is a key to the other table. In other words, it would be a 1:1 or 1:* depending on constraints. Let's call it an Customer/Orders relationship to give it some context. However, let's say the requirement is that there be no referential integrity because the table needs to be archived without affecting the rest of the system. In our case, they want to archive the customers, but leave the orders in the system. Ignoring the fact we now have dangling references, which are just a fact of life when you need to prune systems in this way. What would you call this kind of "loose" relationship where there is no actual FK? How might you illustrate this in an ERD without implying there is a FK? But still wishing to show that there is a relationship? I realize that the proper method might be to use an FK anyways, and null the reference when archiving the customer, but that adds extra complexity they don't want to deal with. Further, they don't want to update the orders table after it has been finalized. They want to be able to find that customer number and go back in the archive and find the customer if need be.

retrieving data speed tweaks MS SQL 2005 Posted: 13 Sep 2013 12:25 PM PDT I have a database in Microsoft SQL Server 2005. I have table with 3 columns, namely HASHKEY (BIGINT), NOTE_ID (INT) and TIME_OFFSET (INT). Columns HASHKEY has 19 digit BIGINT values, which can DUPLICATE. But values will be always of 19 digits. I have application mainly depends on data in this table. Application retrieves data from this table with query like: select HASHKEY, NOTE_ID, TIME_OFFSET from TABLE_NAME where HASHKEY in (<list of around 30000 hashkeys>) But this query takes around 2 minutes to retrieve data. This is my problem. The application is real time processing application, and need to retrieve data in about 5 seconds. How can I tweak things in server, so time to retrieve data can be decreased? I have indexed the table by HASHKEY column when I created table, but still retrieving data is taking much time. Is there any setting which I can do in database to so time can be decreased? I will welcome any type of solution. But I need to solve this. I am not very expert in this. Also HASHKEY is just random values of 19 digits, no relation with other values. Result of following query is, select count(HASHKEY) from TABLE_NAME go select count(distinct(HASHKEY)) from TABLE_NAME Result: 225899932 189200251 Time taken: 2 minutes, 1 second EDIT This is script to create table: USE [fp] GO /****** Object: Table [dbo].[fp_core] Script Date: 09/13/2013 22:40:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[fp_core]( [hashkey] [bigint] NOT NULL, [note_id] [int] NOT NULL, [timeoffset] [int] NOT NULL ) ON [PRIMARY] I will ask random set of around 30000 hashkeys for data, no any ordering. When I was having around 20000000 rows in table, query was taking less than 2 seconds, but now retrieval time is increasing. EDIT And here is script to create index, only one index in this table. USE [fp] GO /****** Object: Index [IX_fp_core] Script Date: 09/13/2013 23:04:24 ******/ CREATE NONCLUSTERED INDEX [IX_fp_core] ON [dbo].[fp_core] ( [hashkey] ASC )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] Please help me.

Converting .TPS (TopSpeed) to SQL Posted: 13 Sep 2013 10:26 AM PDT I have an older application that uses TopSpeed as the database engine. I want to use some of the data contained in these tables in another application that uses SQL. To accomplish this, I purchased the TPS ODBC driver and used Access to move the data from the TPS tables to an SQL database by using the linked tables feature. This works fine, but I'm looking for an automated solution (plus, the Access way is messy). Is there a tool out there that could help?

MySQL: sysbench test - InnoDB vs Memory tables Posted: 13 Sep 2013 08:40 AM PDT I've done some tests in order to investigate performance issue on the new HP Gen8 server (Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz) I've created two tables, first one is using InnoDB storage engine and the second one is in Memory - heap table. System details: sysbench-0.4.12-5.el6.x86_64 CentOS release 6.4 (Final) Prepare stage: InnoDB # sysbench --db-driver=mysql --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-db=sbtest1 prepare Memory (heap) # sysbench --db-driver=mysql --test=oltp --mysql-table-engine=heap --oltp-table-size=1000000 --mysql-db=sbtest2 prepare Testing stage: Sysbench – read only test – single table with 1 mln rows - data size 559MB (527MB data + 31MB indexes) InnoDB # sysbench --db-driver=mysql --test=oltp --mysql-table-engine=innodb --num-threads=128 --max-requests=100000 --oltp-read-only run Total time: 16.3648s, TPS (transactions per second): 6111.40 Memory (heap) # sysbench --db-driver=mysql --test=oltp --mysql-table-engine=heap --mysql-engine-trx=no --num-threads=128 --max-requests=100000 --oltp-read-only run This test is running much longer and I had to stop it as the load on the server was very high - even if this is in memory table!?.

SQL Query to fetch data from 4 different tables [migrated] Posted: 13 Sep 2013 08:23 AM PDT I have four tables and I need to fetch data from one table with where condition and the output contains ID's from three different tables using those ID's need to get the names of them. Company: CompanyID - PK CompanyName CompanyDescription Users: UserID - PK FirstName LastName Email TaskDetails: TaskID - PK CompanyID - FK of Company.CompanyID TaskStatus: TaskStatusID - PK TaskID - FK of TaskDetails.TaskID Status Details CreatedBy - FK of Users.UserID UpdatedBy - FK of Users.UserID CreatedAt UpdatedAt Need a query to return something like below: CompanyName,Email,Status,Details,CreatedAt,UpdatedAt with where condition on TaskStatus table TaskStatus.UpdatedBy!=1 and TaskStatus.UpdatedAt>'2013-08-01' and TaskStatus.status='COMPLETED'

On Oracle 12c, permitting and disallowing crashrecovery Posted: 13 Sep 2013 09:21 AM PDT To support a developer who is testing his application on an Oracle 12c database, has requested the following for two users: USER0 who is permitted to start XA transactions and do crashrecovery. and USER1 who is permitted to start XA transaction but is not permitted to do crash recovery I have not worked extensively with oracle and I am having a hard time tracking down how to honour this request. Any pointers or references would be appreciated.

Import Oracle schema data without losing modifications of stored procedures Posted: 13 Sep 2013 05:57 PM PDT I have this scenario: One huge (thousands of tables), complex production database, Oracle 8 One huge (thousands of tables), complex development database, Oracle 9 ( same structure as production ) Development database has modified stored procedures and packages as well as new ones Development database has new tables in some schemas Neither Oracle 8 exp nor Oracle 9 imp has CONTENT option We usually do as follows because it's the only way to get data updated properly because a import with ignore=yes would only insert new data, but wouldn't update pre-existing rows with the same PK but different values in non-PK columns: Delete one schema, then create the user again to have an empty schema Import from user to user to the empty schema The problem is: How to update the development database with fresh data from a production export without having to delete the schema first, since there's new stored procedures/packages as well as modified ones in the development database ? The comparing process to re-create only modified or new stored procedures after deleting the schema, for getting them back from a backup, would be too error prone. There are thousands (literally) of tables so we don't want to program a stored procedure to refresh the data in certain order etc. That would take months to write and test. What would be an import-based solution to this ? EDIT: I failed to mention that prod is Solaris and dev is RedHat.

Query getting periodically stuck in 'copying to tmp table' state, never completes Posted: 13 Sep 2013 12:44 PM PDT I am running Wordpress on a dedicated server with a MySQL backend. I have a query that usually takes <1 second to execute, but periodically, this query will get stuck in a 'copying to tmp table' state and stay that way indefinitely until it is either killed or until mysqld is restarted. After restarting mysqld the problem goes away, the (identical) query once again takes <1 second to execute. This leads me to believe this is a configuration problem. How do I go about solving this problem? The query itself is not too intensive, and my server is not experiencing any sudden traffic spikes. The tables themselves are all InnoDB format. Here is my my.cnf: http://pastebin.com/9UMPxfAr The query: SELECT ctt.term_id AS catid, p.ID, p.post_title AS title, GROUP_CONCAT(tt.term_id) as terms_id FROM (SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' AND post_date > '2013-09-09 17:00:00' AND post_date < '2013-09-10 08:14:00') AS p JOIN wp_postmeta AS pm ON (p.ID = pm.post_id AND pm.meta_key = 'wpo_sourcepermalink') JOIN wp_term_relationships AS ctr ON (p.ID = ctr.object_id) JOIN wp_term_taxonomy AS ctt ON (ctr.term_taxonomy_id = ctt.term_taxonomy_id AND ctt.taxonomy = 'category' AND ctt.term_id IN ('8','314','6')) JOIN wp_term_relationships AS tr ON (p.ID = tr.object_id) JOIN wp_term_taxonomy AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id AND (tt.taxonomy = 'post_tag' OR tt.taxonomy = 'post_author')) GROUP BY tr.object_id ORDER BY pm.meta_value ASC; An EXPLAIN of the query: http://pastebin.com/m5ndBfVX And the output of "SHOW ENGINE INNODB STATUS" when a query is stuck in the 'copying to tmp table' state: http://pastebin.com/h0xv4Sfa

Accumulo table design methodology Posted: 13 Sep 2013 10:23 AM PDT I am just getting started with Accumulo and NoSQL databases and I am looking for some discussion on table design. I get the key value structure that is seen in the manual. However, if I am trying to recreate a relational database, I am not sure how relationships work. Can someone explain to some degree how to setup and "Hello World" database (i.e., manager-employee database). I want to use key-value implementation.

Disaster Recovery for PostgreSQL 9.0 Posted: 13 Sep 2013 07:18 PM PDT We have a number of PostgreSQL 9.0 servers. We use binary replication to have a host standby instance of those. The only problem is that is someone drops the master, with or without intentions, this will cascade to the replicas as well. I'm looking at the possible ways to avoid this. One possible option in seems to be Point in Time Recovery. I'm just wondering what could be a good design for this. Any ideas? Let's assume the master is compromised and we lose everything we have there. How can we avoid losing the replica or at least have a way to bring it back if it's dropped?

Oracle Patch Update Posted: 13 Sep 2013 11:20 AM PDT We have an Oracle RAC production environment with primary and secondary DB. Our DBA has asked to update oracle version from 11.2.0.1.0(64 bit) to 11.2.0.3(64 bit) with patch 6880880,10404530,16803769 and 16803775. In our current database we have Shared storage,ACL settings, security settings,Gateway/Heteregenous connectivity, Dataguard, Data broker, Backup policy and Oracle Client installed on other machines. DBA has estimated that he need to do installation, settings from scratch and test.. So, when the version is updated, do we really need to reconfig and install everything (Shared storage,ACL settings, security settings,Gateway/Heteregenous connectivity, Dataguard, Data broker, Backup policy and Oracle Client installed on other machines) ?? If yes its fine, but no then I need to justify it. I can understand testing would be required..

Replicated Database Log File Maintenance Posted: 13 Sep 2013 03:20 PM PDT I have a database on the publisher that is involved in replication (publication configured for merge and transaction). Trying to regain control of the log file for this particular database (VLF count, size, etc.). Is there anything I need to do (or be cautious of) with the replication setup before trying to perform any maintenance on the log file? I am not an expert in the area of replication and cannot find anything solid that provides guidance as to what measures should be taken. Edit: This would include working on the distribution database as well, data retention was not configured at all for some reason.

SQL Server Designers, Failed Saves, and Generated Scripts Posted: 13 Sep 2013 02:09 PM PDT I am a big fan of the simple diagramming tool that comes with SSMS, and use it frequently. When I save changes to the model, I have it configured to automatically generate the change scripts that go along with the save. I then save (and source control) the resulting change script. This works great and an important piece of the process my team(s) uses. What occasionally happens is that a save fails, and I still get the option to save my change script. I then fix the problem and save again (which results in another change script). I'm never clear what I need to do at this point to maintain a consistent set of change scripts. There seems to be overlap between the two scripts (the failed and the successful), but they are not identical. If I want to continue to use this feature, what should I be doing with the resulting script as soon as I get a failed save of the model?

How to avoid empty rows in SSIS Excel Destination? Posted: 13 Sep 2013 08:20 PM PDT Does anyone have a way to avoid empty rows when using SSIS to export to Excel. Here's a simple example of one data flow task: OLE DB Source: Data Conversion (to handle the annoying UNICODE / NON-UNICODE deal): The end result is either of the two below depending on value of "FirstRowHasColumnName" in the Excel Connection Manager. Note, the blank rows.

How to add rows/columns to the table in runtime in SSRS 2008 Posted: 13 Sep 2013 10:20 AM PDT Usually we design the table to have x number of rows and y number of columns in a report. But how can we create a report which adds the rows and columns dynamically at run time based on the result of the source query? For example I want to list stdentId, StudentName and any course each student has enrolled in. As the number of courses is different from one person to the other, I should add the rows and related column for courses at run time based on the query result. How can it be done? For example: Thanks for your help in advance.

How to disable oracle's MAX_ENABLED_ROLES limit Posted: 13 Sep 2013 04:20 PM PDT How to disable oracle's MAX_ENABLED_ROLES limit or expand the value of limitation. [oracle 10g (win32)]

In MySQL, does the order of the columns in a WHERE clause affect query performance,why? Posted: 13 Sep 2013 01:20 PM PDT I have a query that doesn't use any indexes: SELECT 32, guid, 1, 1, 1, 0, 5 FROM test WHERE level >= 20 AND ( ( fun_GetIndexValue(data, 354) >> 16 ) + ( fun_GetIndexValue(data, 355) >> 16 ) + ( fun_GetIndexValue(data, 356) >> 16 ) + ( fun_GetIndexValue(data, 357) >> 16 ) + ( fun_GetIndexValue(data, 358) >> 16 ) + ( fun_GetIndexValue(data, 359) >> 16 ) ) >= 1; The level column has only about 80-90 distinct values, the table test has about million rows, and the data column is passed to the function, so I think the query can not use any indexes. But I found that if I put the level condition in the end, the query performs slower. Why is that?

Delete word, its meanings, its meaning's example sentences from DB Posted: 13 Sep 2013 05:20 PM PDT I have three tables as below (simplified for demonstration): words ===== integer id text word meanings ======== integer id integer word_id text meaning examples ======== integer id integer meaning_id text sentence where, word_id stores id of the word in words table and meaning_id stores id of the meaning in meanings table. I am trying to figure out a sql query, given a word's id, to delete the word with all its meanings and example sentences all at one time. Is such sql query possible to compose? If so, how? Edit1: I am using SQLite3 as the database. Edit2: I figured the following solution which requires 3 sql queries in order: DELETE FROM examples WHERE meaning_id IN (SELECT id FROM meanings WHERE word_id=the_given_id); DELETE FROM meanings WHERE word_id=the_given_id; DELETE FROM words WHERE id=the_given_id; I'm still looking for the answer to my question: is the whole process possible to be done in one query?

MySQL concurrent INSERTs Posted: 13 Sep 2013 08:20 AM PDT I have a MySQL database with InnoDB tables. There are different client processes making SELECT (to check the existence of a value) and INSERT or UPDATE (depending on the result of the select) statements. What I fear is a possible concurrent access to data causing only INSERTs and no UPDATEs. Is LOCK Table WRITE the only solution?

How can I optimize this query and support multiple SKUs? Posted: 13 Sep 2013 12:20 PM PDT My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any suggestions on indexes would be much appreciated also. SELECT available - ( SELECT COALESCE(sum(quantity), 0) FROM product_locks WHERE sku = 'sku1' ) - ( SELECT COALESCE(sum(quantity), 0) FROM orderlineitems INNER JOIN responses_authnets ON responses_authnets.id = orderlineitems.response_id WHERE sku = 'sku1' AND responses_authnets.salesite_id = 'site_id_1' ) AS free, available AS total, sku, on_hold FROM product_inventories WHERE sku = 'sku1' AND salesite_id = 'site_id_1';

How to modify an update in Oracle so it performs faster? Posted: 13 Sep 2013 02:20 PM PDT I have this query: UPDATE ( SELECT h.valid_through_dt, h.LAST_UPDATE_TMSTMP FROM ETL_FEE_SCH_TMP d, FEE_SCHEDULE_HISTORICAL h WHERE h.FUND_ID = d.FUND_ID AND h.FEETYPE_NAME = d.FEETYPE_NAME AND h.BREAKPOINT_TYPE = d.BREAKPOINT_TYPE AND h.BREAKPOINT_QTY = d.BREAKPOINT_QTY AND h.LOW_BREAKPOINT_AMT = d.LOW_BREAKPOINT_AMT AND h.VALID_THROUGH = TO_DATE ('31-DEC-9999', 'dd-mon-yyyy') AND h.universe = 'DC' AND h.universe = d.universe AND EXISTS ( SELECT 1 FROM FEE_SCHEDULE s WHERE s.FUND_ID = h.FUND_ID AND s.FEETYPE_NAME = h.FEETYPE_NAME AND s.BREAKPOINT_TYPE = h.BREAKPOINT_TYPE AND s.BREAKPOINT_QTY = h.BREAKPOINT_QTY AND s.LOW_BREAKPOINT_AMT = h.LOW_BREAKPOINT_AMT AND s.universe = 'DC' ) ) updateTable SET updateTable.VALID_THROUGH = (SYSDATE - 1), updateTable.LAST_UPDATE_TMSTMP = SYSTIMESTAMP; The trouble that I am having is that this query takes a long time to run. I don't know whether it is possible to run this on parallel, or it would be easier to update a cursor in a pipeline function. What would you suggest? This is all the information that I believe it is relevant. This is the execution plan of the internal select: Execution Plan ---------------------------------------------------------- Plan hash value: 57376096 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 306 | 8427 (1)| 00:01:42 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 1 | 306| 8427 (1)| 00:01:42 | | 3 | MERGE JOIN CARTESIAN | | 1 | 192| 8426 (1)| 00:01:42 | |* 4 | INDEX RANGE SCAN | SYS_C000666 | 1 | 96| 2 (0)| 00:00:01 | | 5 | BUFFER SORT | | 3045K| 278M| 8425 (1)| 00:01:42 | | 6 | SORT UNIQUE | | 3045K| 278M| 8425 (1)| 00:01:42 | |* 7 | TABLE ACCESS FULL | FEE_SCHEDULE | 3045K| 278M| 8425 (1)| 00:01:42 | |* 8 | INDEX RANGE SCAN | FEE_SCHDL_IDX1 | 1 | | 1 (0)| 00:00:01 | |* 9 | TABLE ACCESS BY INDEX ROWID| FEE_SCHEDULE_HISTORICAL | 1 | 114 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("D"."UNIVERSE"='DC') 7 - filter("S"."UNIVERSE"='DC') 8 - access("H"."UNIVERSE"='DC' AND "S"."FUND_ID"="H"."FUND_ID" AND "S"."FEETYPE_NAME"="H"."FEETYPE_NAME" AND "S"."BREAKPOINT_TYPE"="H"."BREAKPOINT_TYPE" AND "S"."BREAKPOINT_QTY"="H"."BREAKPOINT_QTY" AND "S"."LOW_BREAKPOINT_AMT"="H"."LOW_BREAKPOINT_AMT") filter("H"."FUND_ID"="D"."FUND_ID" AND "H"."FEETYPE_NAME"="D"."FEETYPE_NAME" AND "H"."BREAKPOINT_TYPE"="D"."BREAKPOINT_UNIT_TY Table data: --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- UNIVERSE|FUND_ID |FEETYPE_NAME |BREAKPOINT_TYPE|BREAKPOINT_QTY|LOW_BREAKPOINT_AMT|HIGH_BREAKPOINT_AMT|FEE_PCT|FEE_SCHDL_SEQ_ID|GROUP_ID|LAST_UPDATE_TMSTMP |VALID_FROM|VALID_THROUGH|INSERT_TMSTMP |JOB_ID| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DC |DC9ZTPLPHO|DeferLoad |Percentage |4 |10000 |300000 |3.14 |780250 |null |1/4/2012 3:59:54 PM|6/23/2012 |12/31/9999 |1/5/2011 3:59:54 PM|666 | DC |DCE86Y8XFU|RedemptionFee|Percentage |9 | 100 |100500 |7.67 |780251 |null |6/4/2012 4:49:54 PM|11/12/2011|12/31/9999 |8/17/2011 2:00:54 PM|666 | DC |DCAYL0KONA|FrontLoad |Percentage |2 |50000 |601500 |5.00 |780252 |null |4/25/2012 4:49:54 PM|8/2/2012 |12/31/9999 |12/19/2012 9:59:00 PM|666 | DC |DC9ZTPLPHO|DeferLoad |Percentage |7 |80000 |900000 |2.24 |780252 |null |4/25/2012 4:49:54 PM|8/2/2012 |12/31/9999 |12/19/2012 9:59:00 PM|666 | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- This is the script of the historical table: CREATE TABLE FEE_SCHEDULE_HISTORICAL ( UNIVERSE VARCHAR2(2 BYTE) NOT NULL, FUND_ID VARCHAR2(10 BYTE) NOT NULL, FEETYPE_NAME VARCHAR2(75 BYTE), BREAKPOINT_TYPE VARCHAR2(50 BYTE), BREAKPOINT_QTY VARCHAR2(10 BYTE), LOW_BREAKPOINT_AMT NUMBER(19,6), HIGH_BREAKPOINT_AMT NUMBER(19,6), FEE_PCT NUMBER(19,6), FEE_SCHDL_SEQ_ID NUMBER NOT NULL, GROUP_ID NUMBER, LAST_UPDATE_TMSTMP DATE NOT NULL, VALID_FROM DATE NOT NULL, VALID_THROUGH DATE NOT NULL, INSERT_TMSTMP DATE NOT NULL, JOB_ID NUMBER NOT NULL ); CREATE UNIQUE INDEX FEE_SCHDL_PK ON FEE_SCHEDULE_HISTORICAL(FEE_SCHDL_SEQ_ID); CREATE UNIQUE INDEX FEE_SCHDL_HST_IDX ON FEE_SCHEDULE_HISTORICAL ( UNIVERSE, FUND_ID, FEETYPE_NAME, BREAKPOINT_TYPE, BREAKPOINT_QTY, LOW_BREAKPOINT_AMT, VALID_FROM, JOB_ID ) CREATE INDEX FEE_SCHEDULE_HST_IDX2 ON FEE_SCHEDULE_HISTORICAL(LAST_UPDATE_TMSTMP) CREATE INDEX FEE_SCHEDULE_HST_IDX3 ON FEE_SCHEDULE_HISTORICAL(VALID_THROUGH) ALTER TABLE FEE_SCHEDULE_HISTORICAL ADD ( CONSTRAINT FEE_SCHDL_PK PRIMARY KEY (FEE_SCHDL_SEQ_ID) ); This is the other table: CREATE TABLE FEE_SCHEDULE ( UNIVERSE VARCHAR2(2 BYTE) NOT NULL, FUND_ID VARCHAR2(10 BYTE) NOT NULL, FEETYPE_NAME VARCHAR2(75 BYTE), BREAKPOINT_TYPE VARCHAR2(50 BYTE), BREAKPOINT_QTY VARCHAR2(10 BYTE), LOW_BREAKPOINT_AMT NUMBER(19,6), HIGH_BREAKPOINT_AMT NUMBER(19,6), FEE_PCT NUMBER(19,6), JOB_RUN_ID NUMBER NOT NULL, FILE_DATE DATE NOT NULL, CYCLE_DATE DATE NOT NULL ) The temporary table is the result of FEE_SCHEDULE_HISTORICAL minus FEE_SCHEDULE

Query to find and replace text in all tables and fields of a mysql db Posted: 13 Sep 2013 06:20 PM PDT I need to run a query to find and replace some text in all tables of a mysql database. I found this query, but it only looks for the text in the tbl_name table and just in the column field. update tbl_name set column=REPLACE(column, 'fuschia', 'fuchsia'); I need it to look in all tables and all fields: (everywhere in the database)

You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610