[T-SQL] Cross Apply in Sql

---

• Cross Apply in Sql
• Help with this query please.
• Killing all queries on all databases
• need help in a stored procedure
• how to alter the existing id values to idetity(1,1)
• Float WTF?!
• Selecting data from 2 tables

Cross Apply in Sql Posted: 09 Jun 2010 05:52 PM PDT HiI have a very small question -- What is the difference between Cross Apply and Inner Join. what is it that can be achieved using Cross Apply that cannot be using Inner Join.Thanks in Advance.Azeem.

Help with this query please. Posted: 29 Aug 2013 04:40 AM PDT Hi All, i have this script:[code="sql"];WITH Estimated AS (SELECT nh.id,nh.parent_id,tproy.prefix,nh.name AS 'PLAN',Levelnodes=1, Jerarquia = CAST('\'+CAST(nh.name AS VARCHAR(200)) AS VARCHAR(MAX)) ,CONVERT(VARCHAR,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))/60)) + ':' + RIGHT('0' + CAST (SUM(CONVERT(decimal(8,4),cdv.value))%60 AS INT),2) AS '[HH:mm]' ,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))) AS 'TotalMinutos' FROM testprojects tproy (NOLOCK)INNER JOIN testplans tplan (NOLOCK) ON tproy.id=tplan.testproject_idINNER JOIN testplan_tcversions tcver (NOLOCK) ON tplan.id=tcver.testplan_idINNER JOIN cfield_design_values cdv (NOLOCK) ON tcver.tcversion_id=cdv.node_idINNER JOIN nodes_hierarchy nh (NOLOCK) ON tplan.id=nh.id WHERE tproy.id=2 AND cdv.value NOT like '%[A-Z]%'GROUP BY tproy.prefix,nh.name,nh.id,nh.parent_idUNION ALLSELECT nh.id,nh.parent_id,tproy.prefix,nh.name AS 'PLAN',Levelnodes=1, Jerarquia = CAST('\'+CAST(nh.name AS VARCHAR(200)) AS VARCHAR(MAX)) ,CONVERT(VARCHAR,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))/60)) + ':' + RIGHT('0' + CAST (SUM(CONVERT(decimal(8,4),cdv.value))%60 AS INT),2) AS '[HH:mm]' ,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))) AS 'TotalMinutos' FROM testprojects tproy (NOLOCK)INNER JOIN testplans tplan (NOLOCK) ON tproy.id=tplan.testproject_idINNER JOIN testplan_tcversions tcver (NOLOCK) ON tplan.id=tcver.testplan_idINNER JOIN cfield_design_values cdv (NOLOCK) ON tcver.tcversion_id=cdv.node_idINNER JOIN nodes_hierarchy nh (NOLOCK) ON tplan.id=nh.idINNER JOIN Estimated e ON nh.parent_id=e.id WHERE tproy.id=2 AND cdv.value NOT like '%[A-Z]%'GROUP BY tproy.prefix,nh.name,nh.id,nh.parent_id)SELECT id,parent_id,name = SPACE((nodes_level -1)*4) + name,nodes_level,jerarquia FROM EstimatedGROUP BY tproy.prefix,nh.name,nh.id,nh.parent_idORDER BY Jerarquia;[/code]give me this error:GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'Estimated'.Thanks

Killing all queries on all databases Posted: 29 Aug 2013 10:20 PM PDT I'm trying to kill all queries on all user databases prior to starting an overnight batch. Running this codeDECLARE @SQL VARCHAR(MAX)SET @SQL = ''SELECT @SQL = @SQL + 'USE master; ' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 'ALTER DATABASE [' + NAME +'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 'ALTER DATABASE [' + NAME +'] SET MULTI_USER; ' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) FROM sys.databases WHERE name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')PRINT @SQLEXEC (@SQL)is giving errors (incorrect syntax near GO). However copying the printed sql and running that works. Can anyone suggest why this would be the case?Thanks

need help in a stored procedure Posted: 29 Aug 2013 05:38 PM PDT Hi friends, I have a table called op_registration where every visit of out patient(op) will be inserted.I need to check count of regular(up to 28 days from registration date) and irregular (more than 28 days) patients for each day in given month..[code="sql"]CREATE TABLE [dbo].[OP_Registration]( [Id] [int] IDENTITY(1,1) NOT NULL, [FacilityId] [int] NOT NULL,--branch id [MRNO] [nvarchar](20) NOT NULL,--patient unique number [OPNO] [nvarchar](20) NOT NULL,-- patient unique number per visit [RegistrationDate] [datetime] NOT NULL, [VisitNumber] [int] NULL, CONSTRAINT [PK_OP_Registration] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO[/code]any help would be appreciated..Thanks in advance..

how to alter the existing id values to idetity(1,1) Posted: 29 Aug 2013 01:36 AM PDT hi,I have created the table likecreate table tblemployee(id int,name varchar(10),gender int)the table does not have any records..now how to add identity seed and increment values like identity(1,1) ..i have tried it is working from table design how to write T-sql query for that.Thanks,Giri

Float WTF?! Posted: 29 Aug 2013 09:18 AM PDT Ok, so this has no real bearing on my application but I'm curious. Given the following:[code="sql"]select len(0.28999999165535)select len(cast(0.28999999165535 as float))select 0.28999999165535 * 2select cast(0.28999999165535 as float) * 2[/code]The results are:1640.579999983310700.5799999833107so..... WHY IS THE LEN OF THE FLOAT 4?! The reason I ask is that it's stored in the database (erroneously) as 0.28999999165535 instead of 0.29 but if I try to find it using a LEN() filter it doesn't filter as expected.[quote][/quote]

Selecting data from 2 tables Posted: 29 Aug 2013 01:43 AM PDT SELECT * FROM DocketTB,WorkshopTB WHERE DocketTB.Docket_Date = WorkshopTB.Date_Raised order by Docket_Date descusing the above works to a fashion but it adds them in 1 row!, how is the correct way to do the data row by row in date order. Hope this makes sense

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

[SQL Server 2008 issues] Creating a database takes a long time due to model db size. Not sure why?

---

• Creating a database takes a long time due to model db size. Not sure why?
• Dynamic SQL and DATEADD with User Defined Variable in Number/2nd position
• Would like to add data in table when sql m/c starts or shutdown
• Create a stored procedure on multiple databases
• Commit
• Hive insert with openquery
• Microsoft SQL 2008 R2 Backup and Restore
• Mult-Part Identifier cannot be bound??
• Bad results from 3 part update
• Error HRESULT E_FAIL has been returned from a call to a COM component. SSIS
• Deleting empty files using ForEach loop container
• Trace - data quantity of query results
• SSIS (2008 R2) Fuzzy Lookup not working. Not matching similar names.

Creating a database takes a long time due to model db size. Not sure why? Posted: 28 Aug 2013 07:46 PM PDT Help! I am confused. The following scenario is happening on our system.I have a 2008 R2 SP2 two node cluster. The SQL Server service account is added to the "Perform volume Maintenance" local security policy option on both nodes.After SQL installation, I always set the model database size to 64 MB data, 64 MB log and set the autogrow sizes for both to be 64 MB , just so that new databases have something a little better than the SQL default. This seems to be causing an issue though:If I execute the following script:CREATE DATABASE [test1] ON PRIMARY ( NAME = N'test1', FILENAME = N'E:\Data\test1.mdf' , SIZE = 1048MB , FILEGROWTH = 65536KB ) LOG ON ( NAME = N'test1_log', FILENAME = N'K:\Logs\test1.ldf' , SIZE = 64MB , FILEGROWTH = 65536KB )The command takes 50 seconds to run.If I set the model database to be a smaller initial size: 2 MB data, 17 MB log, the same command completes in just 3 seconds. I am not sure why there is such a difference in time due to the size of the model database? I have never encountered this issue before.Any help would be great.Thanks.

Dynamic SQL and DATEADD with User Defined Variable in Number/2nd position Posted: 29 Aug 2013 08:28 AM PDT This syntaxSET @sql = 'SELECT * FROM <sometable> WHERE dateadd(hh, ''' + @tzoffset + '''), END_DATETIME) BETWEEN ''' + @StartDate + ''' AND ''' + @EndDate + ''''returns this errorConversion failed when converting the varchar value 'SELECT * FROM <sometable> WHERE dateadd(hh, '' to data type int.How do I get around this?

Would like to add data in table when sql m/c starts or shutdown Posted: 28 Aug 2013 10:04 PM PDT I would like to add data like time in a table in sqlserver when sqlserver machine is started or shutdown.

Create a stored procedure on multiple databases Posted: 29 Aug 2013 05:37 AM PDT How can I create sps on multiple database?

Commit Posted: 29 Aug 2013 07:48 AM PDT It has happened to me for the 2nd time, on two different servers but both were 2008 R2. I tried to do a little research and find out why this happened but there was nothing to be read.So, I start a transaction by [code="plain"]Begin Tran[/code]do my stuffcheck the affected tablesdecide what to do, usually by running [code="plain"]COMMIT[/code]After a while of doing so, sql gets timeouts and the system gets locked. I'm confused as I find out that I still have several uncommitted transactions. The result of running commit was "Command completed successfully".Can anyone tell me what was happening? Why these transactions did not commit even though commit completed successfully?

Hive insert with openquery Posted: 29 Aug 2013 12:56 PM PDT Hi,My question is based on this articlehttp://ayadshammout.com/2013/05/27/import-hadoop-data-into-sql-bi-semantic-model-tabular/Mentioned in the article is an example of using openquery to perform select statement on a Hive table through a linkedserver.I was wondering how can i also perform an insert statement with openquery?I understand that for example to insert into Hive is to use a Load command, like:[code="other"]load data inpath '/tmp/data.csv' overwrite into table tableA;[/code]How do i execute this with openquery? I've tried the example below and some slight variations but all I get in return were syntax errors[code="other"]insert openquery (linkedserverName, 'load data inpath '/tmp/data.csv' overwrite into table tableA;')[/code]Hope to hear from somebody on the correct syntaxThank you,Koh

Microsoft SQL 2008 R2 Backup and Restore Posted: 29 Aug 2013 06:01 AM PDT This is a warning to anyone running SQL 2008 R2.If you back up any of your system databases on version/build (10.50.3720) , they will not be able to be restored on anything except the same build, and IT is no longer available from Microsoft. This service pack came out on May 13, 2012. It was named, "SQL Server 2008 R2 Service Pack 2 Community Technology Preview (CTP)". On July 26, 2012, another service pack 2 came out, "SQL Server 2008 R2 Service Pack 2 (SP2) aka (10.50.4000)".If you try to restore a backup created from the May build, you will not be able to restore on a server running the June build, you will get the following error; "The backup of the system database on the device D:\sqlbackups\msdb.bak cannot be restored because it was created by a different version of the server (10.50.3720) than this server (10.50.4000)."Microsoft's work-around is to remove SQL, reinstall, then install the updates to the same build that the backup was created, but it is no longer available. Is anyone at MS using a brain?Hopefully, Steve Ballmer leaving will improve things, but the track record from MS has not been promising. My boss has been wanting to move to MYSQL, and although I have been trying to discourage it, these kinds of issues make it hard to find reasons to stay with MS-SQL.For the money we pay for SQL, there should be more value in the products they push out. I guess they expect us all, their paying customers, be be their beta testers.

Mult-Part Identifier cannot be bound?? Posted: 29 Aug 2013 02:08 AM PDT Anyone have any ideas on why I'm gettingMult-part Identifier cannot be bound error on the followingThanksJoe[code="sql"]SELECT [rpt_v_ch_all clients].LName + ', ' + ISNULL([rpt_v_ch_all clients].FName, ' ') AS clientname ,dbo.[rpt_v_ch_all clients].Client_ID ,dbo.[rpt_v_ch_all clients].LName ,dbo.[rpt_v_ch_all clients].FName ,dbo.[rpt_v_ch_all clients].AdmitDate ,dbo.[rpt_v_ch_all clients].DischargeDate ,dbo.[rpt_v_ch_all clients].Code ,dbo.[rpt_v_ch_all clients].AbbrName ,dbo.[rpt_v_ch_all clients].ID AS Expr1 ,dbo.[JBD_V_CH_Info].ENDTIME ,dbo.JBD_V_CH_Info.poscode ,dbo.JBD_V_CH_Info.dmcode ,dbo.JBD_V_CH_Info.dmabbr ,dbo.JBD_V_CH_Info.posabbr ,dbo.JBD_V_CH_Info.CAD226 ,dbo.JBD_V_CH_Info.CAD204 ,dbo.JBD_V_CH_Info.CAD205 ,dbo.JBD_V_CH_Info.CAD207 ,dbo.JBD_V_CH_Info.CAD227 ,dbo.JBD_V_CH_Info.FullName ,dbo.JBD_V_CH_Info.BOX ,dbo.JBD_V_CH_Info.OCount ,dbo.JBD_V_CH_Info.SCount ,dbo.JBD_V_CH_Info.[Days Diff] ,dbo.JBD_V_CH_Info.RECORDED_SERVICE_VOIDED_MONIKER ,dbo.JBD_V_CH_Info.datepartofstarttime ,dbo.JBD_V_CH_Info.CLIENT_IDENTIFIER ,dbo.JBD_V_CH_Info.Expr1 AS Expr3 ,dbo.JBD_V_CH_Info.CIEffdate ,dbo.JBD_V_CH_Info.CIexpdate ,dbo.JBD_V_CH_Info.ID ,dbo.JBD_V_CH_Info.STARTTIMEFROM dbo.[JBD_V_CH_Info]RIGHT JOIN dbo.[rpt_v_ch_all clients] ON dbo.JBD_V_CH_Info.ID = dbo.[rpt_v_ch_all clients].ID[/code]

Bad results from 3 part update Posted: 29 Aug 2013 05:05 AM PDT I recently finished reading this article wrote by [i]Mr Jeff Moden[/i].[url=http://www.sqlservercentral.com/articles/T-SQL/68467/]http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]We wants to add "Multiples of 3" in our table like 3, 6, 9, 12. So we made a table for this goal with this structure:[code="sql"]create TABLE temp(RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, SomeInt INT, Expected INT) INSERT INTO temp (SomeInt, Expected) SELECT 0,3 UNION ALL SELECT 0,6 UNION ALL SELECT 0,9 UNION ALL SELECT 0,12[/code]Now we insert 3, 6, 9, 12 into our table with this query without any problem:[code="sql"]DECLARE @N INT, @Anchor INT SELECT @N = 0 UPDATE temp SET @N = SomeInt = @N + 3, --Adds 3 to N and updates SomeInt with N @Anchor = RowNum FROM temp OPTION (MAXDOP 1)[/code]But this query return unexpected results:[code="sql"]DECLARE @N INT, @Anchor INT SELECT @N = 0 UPDATE temp SET @N = @N + 1, --Adds 1 to N @N = SomeInt = @N + 2, --"Forgets" to do @N + 2 after first row @Anchor = RowNum FROM temp OPTION (MAXDOP 1)[/code][i]Jeff Moden[/i] mentioned "There's a very long winded explanation about this that I'll very gratiously step aside".And also he mentioned "YOU MUST HAVE AN "ANCHOR" COLUMN" like @Anchor = RowNum. He also said "there are some unpredictable cases where an error can occur without anchor"Now I want to know why? I want to know the exact reasons. I want to realize it logically.What makes 3 part update sometimes don't return appropriate results?Also why I have to use an anchor in my query while logically there is no need of it?Warmly thank you for your time and help

Error HRESULT E_FAIL has been returned from a call to a COM component. SSIS Posted: 29 Aug 2013 04:34 AM PDT I have an SSIS package that is connecting to a database using an ODBC connection. The connection is setup and tests successfully. If I create a simple Execute SQL Task and run:select top 1000 *from [MyTableData] where DateEntered >= GETDATE()-45it runs fine. If I run:deletefrom [MyTableData] where DateEntered >= GETDATE()-45Then I get this:failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Any ideas? The connection must be fine because it will do a select just fine, but it won't delete.

Deleting empty files using ForEach loop container Posted: 29 Aug 2013 03:23 AM PDT I have a package that is building MANY flat files. Once the files are built, I would like to use a ForEach loop container, and delete ONLY the empty files. The package will produce both files with data and empty files. Can I do this in a ForEach? Thank you.

Trace - data quantity of query results Posted: 29 Aug 2013 01:58 AM PDT I have been experimenting with creating and replaying traces for a web application - investigating some performance issues. The trace works fine, using sp_trace_create, though the sp_trace_setevent values are a bit of a mystery, but seem to work.Now we have identified missing indexes etc, attention is turning to the quantity of data going from SQL Server to the web application.Is there an easy way to find the amount of data selected, number or rows/ record sizes? Could it be requested in those mysterious sp_trace_setevents?

SSIS (2008 R2) Fuzzy Lookup not working. Not matching similar names. Posted: 29 Aug 2013 02:16 AM PDT Hello,I am running a very simple SSIS package with a fuzzy lookup that is not working. I am RDP'ing to the server (Windows Server 2008 R2 Ent) with SQL Server 2008 R2. I simply want to create a listing of all customer names and indicate if they are similar to other names (for example, ABC Inc is similar to ABC Inc.). I am comparing on a single column (Name).When I run the package, it basically returns _Similarity = 1 for almost all names -- even though they are clearly similar. I have similarity threshold set to .7 and delimiters are space, carriage return, tab, and line feed. My data source is a table using OLE DB.Here are partial results to illustrate what is happening:Name _Similarity _ConfidenceABC CORPORATION 1 1ABC CORPORATION 1 1ABC CORPORATION OF AMERICA - CEJ502453 1 1ABC CORPORATION OF AMERICA - CEJ502870 1 1ABC CORPORATION OF AMERICA, 1 1ABC CORPORATION OF AMERICA, 1 1ABC CORPORATION OF AMERICA, 0.9875 0.5ABC CORPORATION/ MORVEN PARK 1 1Sorry about the poor formatting but as you can see _similarity is 1 for all but a single name. Based on previous uses of this tool, I would expect ABC CORPORATION to be 1 and the remainder to be < 1, depending on the similarity.I have found some articles online reporting similar behaviour but they seem to be having this problem when the data source is an Excel file. This is related to 32 bit office and 64 bit SSIS. I'm using OLE DB for my data source, not Excel.I've experimented with different reference table settings, etc. -- all to no avail.What am I missing.Any help would be appreciated.Let me know if I left any pertinent info out.Thanks,Brett

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] Unable to DROP Database

---

• Unable to DROP Database
• How do you handle cross-validating inputs vs database information?
• Collation changes after some time
• Upgrading MySQL 5.1 to 5.5 on Centos 6 without removing dependencies
• How do I get security information out of an MKF database? (FileNet Security Database)
• Target database memory exceed available shared memory
• Transaction log autogrowth duration
• Getting the most out of SQL Server with a million rows a day
• SSIS Connection String Expression at Run Time and during Execution
• Deadlock from mysqldump --single-transaction, is it possible?
• Why would running a SQL query overnight break my database for the following day?
• Slow query and Insert with trigger
• Replicating from master to slaves and from slaves to master
• how to create an incremental database copy in postgresql?
• .bak file not visible in any directory
• Minimizing Page Fetches
• Query to get reposts from people the user is following
• Need to install Oracle Express 11g Release 2 on a Windows 7 64-bit laptop
• Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!
• USER_ID field in alert logs (also in V$DIAG_ALERT_EXT view)
• Idle connection plus schema-modifying query causing locked database
• Bitmask Flags with Lookup Tables Clarification
• SQL Server 2012 catalog.executions to sysjobhistory - any way to join them?
• SQLite writing a query where you select only rows nearest to the hour
• Listing the existing SQL Server Logins and Users
• slow load speed of data from mysqldump
• Add Oracle Label Security to an existing Oracle installation
• How can I track database dependencies?

Unable to DROP Database Posted: 29 Aug 2013 09:00 PM PDT I get the '..currently in use' message when I try to drop my database. I've restarted my SQL Server Express service a couple of times, restarted the computer a couple of times, and even tried to kill the process using the database. Nothing has worked so far. When I do kill xx nothing happens the first time, however when I do it again, it comes up with not an active process message, as if it had marked the process for deletion the first time I did the kill. The entry still shows up in sysprocesses table. I want to know what is the safest way to drop my database? Environment: sql server 2008R2 EXPRESS SSMS is not an option, I am using sqlcmd..

How do you handle cross-validating inputs vs database information? Posted: 29 Aug 2013 02:42 PM PDT I have created a reporting engine and it is up and running pretty good right now. It consists of a lot of employee data and is used to many things. An example is that we would take a report from a vendor about usage on their platform and my database would add on an extra 5-6 fields that would be used to cross-reference groups, titles, whatever. Most vendors just report out user# or email so internal managers want more info. Works great. However it is not real time. It is a manual process right now where I take the files from vendor, load them into table, then execute my query to give them their new file. Another example: Admin runs 50 distribution lists that might have a few thousand emails on them. She sends me her lists to be cross-validated against the current db. I have queries to do this automatically. But again same process as before. I know I can schedule the query to fire off whenever but don't want to bog down server which is hosting about 10 internal sites. Two issues here: 1. How do I deal with the cross-validation? Should this be handled via a web applet? What is the logic that I need in place? 2. How do you validate reports (csv xls) that are imported to a table?

Collation changes after some time Posted: 29 Aug 2013 02:28 PM PDT I have a table which is being targeted by outside script to write stuff into. Default collation if utf8_general_ci but to connect it with foreign key to another table I need to convert it to utf8_unicode_ci. Every time I change it to unicode on and select "Convert Data" (in HeidiSQL) it looks good in the code and alters it properly. Strangely, few minutes after id check and it would be back to general utf.

Upgrading MySQL 5.1 to 5.5 on Centos 6 without removing dependencies Posted: 29 Aug 2013 06:59 PM PDT I'm trying to upgrade MySQL 5.1 to 5.5 using yum/rpm combo (yum to remove MySQL 5.1 and rpm to install MySQL 5.5). Most instructions I've found suggest to do first: yum remove mysql mysql-server mysql-libs However doing so will remove in my case lots of other packages which I definitely don't want to remove: ================================================================================ Package Arch Version Repository Size ================================================================================ Removing: mysql x86_64 5.1.69-1.el6_4 @updates 2.4 M mysql-libs x86_64 5.1.69-1.el6_4 @updates 4.0 M mysql-server x86_64 5.1.69-1.el6_4 @updates 25 M Removing for dependencies: cronie x86_64 1.4.4-7.el6 @cr 166 k cronie-anacron x86_64 1.4.4-7.el6 @cr 43 k crontabs noarch 1.10-33.el6 @cr 2.4 k mysql-devel x86_64 5.1.69-1.el6_4 @updates 388 k percona-toolkit noarch 2.2.4-1 @percona 5.7 M perl-DBD-MySQL x86_64 4.013-3.el6 @base 338 k php-mysql x86_64 5.3.3-23.el6_4 @updates 216 k postfix x86_64 2:2.6.6-2.2.el6_1 @base 9.7 M sysstat x86_64 9.0.4-20.el6 @base 807 k Transaction Summary ================================================================================ How can I upgrade MySQL without removing those packages? Can I somehow remove MySQL 5.1 without dependencies (well, maybe besides mysql-devel) and get away with it? (without libraries mismatch). I've found one blog that suggest to upgrade MySQL in yum shell, however I'm not sure if it would change anything? :

How do I get security information out of an MKF database? (FileNet Security Database) Posted: 29 Aug 2013 01:40 PM PDT I'm trying to get the security information for all the documents in our FileNet system. It is stored in a MKF database in hexidecimal format. There's a decode command that will display the group names that have read, write and execute on the document, but I can't figure out how to get that data out of the system. Does anyone here have any FileNet conversion experience?

Target database memory exceed available shared memory Posted: 29 Aug 2013 03:09 PM PDT I am trying to install Oracle 12c on a RHEL 6.4 system with 64GB of memory. When I try to enable automatic memory to 48GB, I get the error: What do I need to change to allow Oracle to use more than 32189MB? [INS - 35172] Target database memory (49897MB) exceeds available shared memory (32189MB) on the system. /etc/sysctl.conf contains the following so I'm not sure why it won't allow me to use more than 32G of shared memory # Controls the maximum shared segment size, in bytes kernel.shmmax = 68719476736 # Controls the maximum shared segment size, in bytes #kernel.shmmax = 68719476736 # Controls the maximum number of shared memory segments, in pages kernel.shmall = 16777216 #kernel.shmall = 1073741824 fs.file-max = 6815744 kernel.msgmni = 2878 kernel.sem = 250 32000 100 142 kernel.shmmni = 4096 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 fs.aio-max-nr = 3145728 net.ipv4.ip_local_port_range = 9000 65500 vm.min_free_kbytes = 51200 fs.file-max = 6815744 limits.conf contains the following: oracle soft nproc 2047 oracle hard nproc 32768 oracle soft nofile 32768 oracle hard nofile 65536

Transaction log autogrowth duration Posted: 29 Aug 2013 12:09 PM PDT I am using event notifications to capture data and log file autogrowth events for all databases on my servers. I'm using the data to for analysis of database storage configuration. In looking at the data I've noticed that the average duration for transaction log growth is well above anything I would expect which leads me to think that I'm either misinterpreting the data or overlooking something related to how transaction log autogrowth works. This is an example of a log file growth event that was captured today: <EVENT_INSTANCE> <EventType>LOG_FILE_AUTO_GROW</EventType> <PostTime>2013-08-29T11:14:26.447</PostTime> <SPID>97</SPID> <DatabaseID>32</DatabaseID> <NTDomainName /> <HostName>[cleansed]</HostName> <ClientProcessID>4884</ClientProcessID> <ApplicationName>.Net SqlClient Data Provider</ApplicationName> <LoginName>[cleansed]</LoginName> <Duration>4173000</Duration> <StartTime>2013-08-29T11:14:22.263</StartTime> <EndTime>2013-08-29T11:14:26.437</EndTime> <IntegerData>64000</IntegerData> <ServerName>[cleansed]</ServerName> <DatabaseName>MyDB</DatabaseName> <FileName>MyDB_log</FileName> <LoginSid>[cleansed]</LoginSid> <EventSequence>14637017</EventSequence> <IsSystem /> <SessionLoginName>[cleansed]</SessionLoginName> </EVENT_INSTANCE> Since duration is reported in miliseconds I'm reading this as it taking 69.54 minutes to grow the file. Autogrowth for this log file is set to 512MB (limited to 2TB) SELECT growth AS GrowthPages, growth*8/1000 AS GrowthMB, max_size, is_percent_growth FROM MyDB.sys.database_files WHERE type=1 GrowthPages GrowthMB max_size is_percent_growth 64000 512 268435456 0 All of the databases log to the same volume which is connected to a SAN via fiberchannel. (I'd have to get with our SAN admin for more details on the storage config if needed). The instance is SQL 2012 Enterprise, server is Windows 2008 R2 Enterprise. Why would it take over an hour to grow the log by 512MB? We're not noticing a delay in operations on any of these databases (unless we're just overlooking it). There are a handful of other databases with similar duration; their autogrowth settings are the same. Other databases with smaller autogrowth settings have proportionally smaller durations.

Getting the most out of SQL Server with a million rows a day Posted: 29 Aug 2013 02:06 PM PDT I have a windows service that is inserting 1000 rows at 1 minute intervals to a SQL Server 2008 Standard database table that looks like: id, datetime, key_id, value key_id is a foreign key to another table that looks like id, name I'm building a localhost website reporting interface in PHP. I will be doing queries from this interface like: Last 7 days of data for key_id's 1,2,50,377 at 1 minute intervals Last 30 days of data for key_id's 40,47,732,400,43,22,18,5,14 at 1 hour intervals 1 hour intervals for key_id 7,8,20,40 for all of May 2009 And I want it to be as fast as possible. How should I tune SQL Server to be performant in this case?

SSIS Connection String Expression at Run Time and during Execution Posted: 29 Aug 2013 07:50 PM PDT I have an Excel connection manager which should write to a file which does not exist prior to runtime but rather is created during execution. I have an expression set for the connection string property of the excel connection manager which points to the file that will be created. The problem I am having is that since the file does not yet exist (prior to execution), when I go to run the package it is giving me an error since it can not find the file. If i create a dummy file and place it where the excel connec manager is pointing the package will run ok. What I have tried to do is point the excel connection manager to an existing file prior to run time since it will not give an error and then hopping it will then pick up the new Path (excel connection string) from the Expressions but what is happening is that since I have an expressions set for the connection string property it will not let me overwrite the connection string property, it keeps defaulting to the value of the expressions which contains other variable that are populated during run time so that is where I am getting my error. The expressions string prior to runtime is not pointing to an actual file because it does not yet exist but mainly because the expressions is not fully populated until runtime since it also contains variables. As of right now it seems like I might have to just point to a dummy template file (without using any expressions) prior to runtime and then maybe use a Script to change the connection string property of the excel connec manager to = a variable containing an expression containing the path to the file.... Does this sound like the way to go or am I over complicating things?

Deadlock from mysqldump --single-transaction, is it possible? Posted: 29 Aug 2013 02:39 PM PDT Is it possible to get a deadlock if you ran mysqldump with the argument --single-transaction ? or any other complication ? If it is used to backup a live site with many other querys running at the same time. Extra info: The mysqldump process could take over few minutes to complete. The tables are innodb. EDIT I am concerned about the deadlocks errors that could happen on the live applications relying on the database during the time where mysqldump is running.

Why would running a SQL query overnight break my database for the following day? Posted: 29 Aug 2013 12:23 PM PDT I apologise in advance if this is a duplicate question (which I bet it will be). I had a good search through and found similar questions but nothing that seemed an exact match. I wrote a report that takes around 30 minutes to run. It does a lot of number crunching and I am quite happy that it takes so long to execute but this means the report can't be interactive. Basically the report has to calculate a profit and loss report as if the company has lost exactly one client. Then it puts that client back and runs the report again as if the next client was lost, etc. I think 30 minutes is actually rather good performance considering how intensive this is. Users are happy with this report being updated nightly and then they can see the cached results the following day. So the plan was to run the report overnight and save the output somewhere. I wrote a stored procedure to do this and I can run it during the day in around 30 minutes. However, when the report runs overnight as a SQL Agent job it NEVER completes. It kicks off at 9pm and when I get into work the next day I will find my inbox flooded with emails saying that SQL Server is broken, isn't performing properly, etc. I kill the job and everything goes back to normal. Except it doesn't go back to normal. What happens next is that ANY report that is run against the same database will timeout. The only way to correct this is to either wait a day (without running the overnight report) or to run another query against the same database directly from SSMS as this seems to reset the problem for some reason. Obvious questions: is it the SQL Agent job causing the problem (maybe permissions)? No, if I run the SQL Agent Job during the day it works fine, it's some sort of timing issue I think. have you tried moving the schedule? I have tried everything from running it at as early as 6pm to running it at as late as 6am and it doesn't seem to make any difference. what else runs overnight? There are a whole load of other jobs that run for different databases. I have moved the schedule around so my job runs in isolation (in theory). There is a job that runs an index optimisation across ALL databases but I can't turn this job off as it is managed externally. Does the query plan change for other reports? I have no way to tell, if I run a report via SSMS then it works, if I run it via the Excel add-in that does exactly the same thing, but via a SQL connection then it times out. I can view the process in Activity Monitor and watch it run for ages before it eventually dies. My personal opinion is that the query engine is making bad decisions about which index to use but when I run a report interactively from SSMS this somehow resets "something" internally and it goes back to running reports using the right indexes again. I have no evidence to back this up with apart from the behaviour is the same if I disable one of the indexes on one of the "raw data" tables. When I say, "run a report interactively" I just mean this: SELECT * FROM FormatMonthEndReport(2013, 1); Where FormatMonthEndReport is a table-valued function. I don't know if this is even relevant but I make a lot of use of APPLY in all of these reports. I imagine more detail will be asked for but I don't want to add too much detail at this stage as this would rapidly turn into a wall of text. I will come back and see what is asked for first so my detail is more targetted. Edit - it looks like running a SQL Profile is going to be the first step. I am going to set this up to run overnight and then report back tomorrow. I need to leave so I won't have time to respond to the other suggestions until then... but I am not ignoring you!

Slow query and Insert with trigger Posted: 29 Aug 2013 01:03 PM PDT I have vehicle tracking system application based on PHP, SQL Server 2008 R2 Enterprise Edition with XEON HP Z800 Server. My database is growing day by day in millions and it makes it very slow to select and insert, I have one main table name TraceData that contain all records for each signal from devices every second more records coming in this table, but when we trying to generate History or report for any vehicle it got stuck and take very long some times more then 5 mins the structure of my table is mentioned below: CREATE TABLE [dbo].[TraceData]( [AutoID] [bigint] IDENTITY(1,1) NOT NULL, [IMEI] [varchar](20) NOT NULL, [Alram] [varchar](20) NULL, [GPRMCState] [varchar](10) NULL, [Lat] [decimal](18, 6) NULL, [NS] [varchar](10) NULL, [Lng] [decimal](18, 6) NULL, [WE] [varchar](10) NULL, [Speed] [decimal](18, 4) NULL, [Direction] [decimal](18, 1) NULL, [PDOP] [decimal](18, 1) NULL, [HDOP] [decimal](18, 1) NULL, [VDOP] [decimal](18, 1) NULL, [RTC] [datetime] NULL, [VCS] [varchar](10) NULL, [VBV] [decimal](18, 2) NULL, [VCV] [decimal](18, 2) NULL, [ADA] [decimal](18, 2) NULL, [ADB] [decimal](18, 2) NULL, [LACCIL] [varchar](10) NULL, [LACCIC] [varchar](10) NULL, [Temperature] [decimal](18, 1) NULL, [Mileage] [decimal](18, 4) NULL, [ModelType] [nvarchar](50) NULL, [Serial] [varchar](10) NULL, [TrackerTime] [datetime] NULL, [ServerTime] [datetime] NULL, [Address] [nvarchar](2000) NULL, [IO1] [varchar](10) NULL, [IO2] [varchar](10) NULL, [IO3] [varchar](10) NULL, [IO4] [varchar](10) NULL, [IO5] [varchar](10) NULL, [IO6] [varchar](10) NULL, [IO7] [varchar](10) NULL, [IO8] [varchar](10) NULL, [IO9] [varchar](10) NULL, [IO10] [varchar](10) NULL, [IO11] [varchar](10) NULL, [IO12] [varchar](10) NULL, [Ignition] [int] NULL, [Multiple] [int] NULL, [First] [int] NULL, [MultipleSession] [bigint] NULL, CONSTRAINT [PK_TraceData] PRIMARY KEY CLUSTERED ( [AutoID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[TraceData] ADD CONSTRAINT [DF_TraceData_Engine] DEFAULT ((0)) FOR [Ignition] GO ALTER TABLE [dbo].[TraceData] ADD CONSTRAINT [DF_TraceData_Multiple] DEFAULT ((0)) FOR [Multiple] GO ALTER TABLE [dbo].[TraceData] ADD CONSTRAINT [DF_TraceData_First] DEFAULT ((0)) FOR [First] GO ALTER TABLE [dbo].[TraceData] ADD CONSTRAINT [DF_TraceData_MultipleSession] DEFAULT ((0)) FOR [MultipleSession] GO I have one trigger on it also that's ALTER TRIGGER [dbo].[update_trackers_table] ON [dbo].[TraceData] AFTER INSERT AS if EXISTS (SELECT * FROM inserted WHERE IO5 = '1' OR (IO3 ='1' AND ModelType = 'GT08' )) BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. -- Set Replication insert into TraceDataApp (IMEI,Alram,GPRMCState,Lat,NS,Lng,WE,Speed,Direction,Mileage,ModelType,Serial,TrackerTime,ServerTime,IO1, IO2,IO3,IO4,IO5,IO6,IO7,IO8,IO9,IO10,IO11,IO12,Ignition) SELECT IMEI,Alram,GPRMCState,Lat,NS,Lng,WE,Speed,Direction,Mileage,ModelType,Serial,TrackerTime,ServerTime,IO1, IO2,IO3,IO4,IO5,IO6,IO7,IO8,IO9,IO10,IO11,IO12,Ignition FROM inserted; -- End Set Replication UPDATE trackers SET LastAlarm = inserted.Alram, LastLangtitute=inserted.Lng, LastLatitute=inserted.Lat, LastTime =DATEADD(HOUR,4,inserted.TrackerTime), LastSpeed= (inserted.Speed * 1.852), TMileage= (trackers.TMileage + inserted.Mileage), LastDirection=inserted.Direction , IGN='On', LastTraceID=inserted.AutoID FROM trackers,Inserted WHERE trackers.IMEI = inserted.IMEI; END else BEGIN -- Set Replication insert into TraceDataApp (IMEI,Alram,GPRMCState,Lat,NS,Lng,WE,Speed,Direction,Mileage,ModelType,Serial,TrackerTime,ServerTime,IO1, IO2,IO3,IO4,IO5,IO6,IO7,IO8,IO9,IO10,IO11,IO12,Ignition) SELECT IMEI,Alram,GPRMCState,Lat,NS,Lng,WE,Speed,Direction,Mileage,ModelType,Serial,TrackerTime,ServerTime,IO1, IO2,IO3,IO4,IO5,IO6,IO7,IO8,IO9,IO10,IO11,IO12,Ignition FROM inserted; -- End Set Replication --Set Replication --insert into TraceDataApp (AutoID,IMEI,Alram,GPRMCState,Lat,NS,Lng,WE,Speed,Direction,Mileage,ModelType,Serial,TrackerTime,ServerTime,Address,IO1, -- IO2,IO3,IO4,IO5,IO6,IO7,IO8,IO9,IO10,IO11,IO12,Ignition) SELECT AutoID,IMEI,Alram,GPRMCState,Lat,NS,Lng,WE,Speed,Direction,Mileage,ModelType,Serial,TrackerTime,ServerTime,Address,IO1, -- IO2,IO3,IO4,IO5,IO6,IO7,IO8,IO9,IO10,IO11,IO12,Ignition FROM inserted; -- End Set Replication UPDATE trackers SET LastAlarm = inserted.Alram, LastLangtitute=inserted.Lng, LastLatitute=inserted.Lat, LastTime =DATEADD(HOUR,4,inserted.TrackerTime), LastSpeed= (inserted.Speed * 1.852), TMileage= (trackers.TMileage + inserted.Mileage), LastDirection=inserted.Direction , IGN='Off', LastTraceID=inserted.AutoID FROM trackers,Inserted WHERE trackers.IMEI = inserted.IMEI; END any help will be really appreciated.

Replicating from master to slaves and from slaves to master Posted: 29 Aug 2013 04:27 PM PDT I have a master and multiple slave machines. All SQL operations on the master node should be sent to the slave nodes. But the tables on the slave nodes may also be written to locally. These local changes on the slave node should be reflected to the master node. For example let's say I have a master machine, lets call it "M", and two slave machines lets call them "A" and "B" All these machines have a table named test_table with a column named "id". I insert data to the test_table on M machine. (M)test_table |id| |1 | (A)test_table |id| (B)test_table |id| now this change is reflected to the slaves: (M)test_table |id| |1 | (A)test_table |id| |1 | (B)test_table |id| |1 | Now on slave A, I make a local change. (M)test_table |id| |1 | (A)test_table |id| |2 | (B)test_table |id| |1 | Now this change is reflected to the master server: (M)test_table |id| |2 | (A)test_table |id| |2 | (B)test_table |id| |1 | And then master server replicates this change to the slaves: (M)test_table |id| |2 | (A)test_table |id| |2 | (B)test_table |id| |2 | What is this kind of replication named? And how can I achieve this on postgresql?

how to create an incremental database copy in postgresql? Posted: 29 Aug 2013 04:18 PM PDT Virtual machine software like VirtualBox allow one to make incremental VM clones. That is data, once "touched" (opened writable), will be copied and stored in the incremental cache of the new clone. I am searching for the same option for a Postgres DBMS. How can I setup an incremental database copy, where entries are read from the original database, and touched/modified rows from the local copy? If not on the DBMS level, how can I emulate such behavior at the file-system/storage level using a separate DBMS instance? Background: The idea is to utilize the powerful database-server yet without incurring much resource overhead for a staged/developer database-copy. Feel free to edit the subject or post to improve clarity. Thanks.

.bak file not visible in any directory Posted: 29 Aug 2013 04:17 PM PDT I have a .bak file created today by someone else, manually created through SSMS 2008 R2. I'm trying to manually restore the database, unfortunately the file isn't appearing when I go to browse it. I can script the restore process, but I've seen this problem before and I'm not sure what could cause the .bak to not appear.

Minimizing Page Fetches Posted: 29 Aug 2013 02:17 PM PDT I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use correlated sub-queries to check the state of flags in other tables to determine Eligibility, or am I better of creating Views that utilize Joins that represent the valid Records? As a more concrete example, is this: Select Col1, Col2, Col3 From Table1 Where (Select RefCol From Table2 Where Table2.PK = Table1.FK) = "Condition" Superior or Inferior to something like this: Select T1.Col1, T1.Col2, T1.Col3 From T1 Inner Join T2 On T1.FK = T2.PK Where T2.RefCol = "Condition" -- Edited -- As a corollary question: Is it productive to create Views which contain Intermediary validations? IE, if I repeatedly need to check if T1.Col1 = 1 and T1.Col2 = 0 is it worthwhile to create the following view: Create View T1Validated As Select Col1, Col2 From T1 Where Col1 = 1 And Col2 = 0 And then later use the existence of a record in T1Validated in subsequent checks, or is that likely to produce additional database page retrievals and/or table scans?

Query to get reposts from people the user is following Posted: 29 Aug 2013 01:17 PM PDT I have a table posts where all the posts by users are stored, the structure of this table is as follows | post_id | post_user | post_content | post_date | the users table is as follows | user_id | username | user_joined | user relationship table is as follows | follower | following | rel_date | this is the query I am using to get the posts from people that user is following to show them. SELECT * FROM posts WHERE post_user in(SELECT follower from user_follow where following=$loggedin_user) Now I want users to share posts, for which I created a table repost_user as follows | repost_user | original_post_user | post_id | repost_date | I want to get posts from people that user following, which includes reposts too.. How do I do this? EDIT : How my resultset should look post_id | post_content | post_user | post_date | is_repost | repost_user | repost_date for eg if its normal post the row should look like 23 | <some content> | 3 | <post date> | false | null | null | if its a repost the row would be 23 | <some content> | 3 | <post date> | true | 2 | <repost_date> |

Need to install Oracle Express 11g Release 2 on a Windows 7 64-bit laptop Posted: 29 Aug 2013 07:17 PM PDT I need the Oracle 11g Release 2 sample schemas (HR, OE, etc.) in order to do most of the available online tutorials. I was hoping to install Oracle Express Edition on my Windows 7 laptop to get these; but I have never heard of anybody successfully installing Oracle XE on a 64-bit Windows platform. Is there a version of Oracle XE 11g R2 available for Windows 7? And if so, could you please point me to it? Thanks...

Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour! Posted: 29 Aug 2013 06:17 PM PDT I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour. I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has 1000 columns mostly boolean and a few varchar. Currently the fastest way i've found to get the information into my DB until now was: Process raw data into CSV files. Load Data In File to rawData Table. Insert rawData table into Table1. (on dupe key do my function) Truncate rawData Repeat. Worked fine until im merging 6M+ Rows into 100M rows and expecting it to take under an hour. I got 16G of ram so I set my Key_Buffer_Pool to 6G. I have my query cache pool to 16M I have my query cache limit to 10M I would just replace the information however it has to be an Upsert, Update the fields that are true if exists and insert if it does not. Things im looking into atm; - Possibly switching server table to InnoDB? |-> Not sure about the performance, as the insert into an empty table is fine, its the merge that's slow. Maybe allowing more table cache? Or even Query Cache? mysql sql mysqli innodb myisam Merge Code: b.3_InMarket = (b.3_InMarket OR r.3_InMarket), To compare my 2 bool columns. Update Ok I set Raid0 Changed my query to Lock Write on tables when inserting When importing csv im disabling keys then re-enabling them before upsert. Changed concurrent_insert to 2

USER_ID field in alert logs (also in V$DIAG_ALERT_EXT view) Posted: 29 Aug 2013 12:17 PM PDT Does anyone know what triggers the USER_ID field in the log.xml to be populated? The value also exists in the V$DIAG_ALERT_EXT view. I've found by observing the logs that if a temp tablespace fills up, it will log the USER_ID of the problematic SQL statement causing the issue. But other than that, it appears that value is always NULL.

Idle connection plus schema-modifying query causing locked database Posted: 29 Aug 2013 05:51 PM PDT As part of our automated deployment process for a web app running on a LAMP stack, we drop all our triggers and stored procedures and recreate them from source control. It turns out there was a hidden danger to this approach that we hadn't thought about. A few days ago we managed to end up with the database for (the staging version of) our web app stuck in a horribly hung state after the following sequence of events: I connect to the remote database from our office (via Python's MySQLdb, as it happens) and run a few SELECT queries on the Foo table. I leave the connection open, because I'm lazy. My boss commits some changes on his laptop, pushes to the remote repo on the web server, and goes to lunch without looking at the output The deployment hook on the web server tries to update the triggers and stored procedures in the database, but isn't able to even DROP the first trigger because the trigger involves the Foo table, which my currently sleeping connection had previously done some SELECTs from. Now nobody can SELECT from the Foo table at all, because the connection trying to DROP the trigger has already taken out a lock on the Foo table that prevents any other connections from accessing the Foo table in any way - even though it's still waiting for the sleeping connection to be closed before it can actually do anything. Crucial business processes relying upon the Foo table grind to a halt, alarms sound, and our web app stops serving customers. My boss flies into a rage and declares that heads will roll if the cause of the problem is not found and fixed so that this can never happen again. (Just kidding, it was only our staging server and my boss is very friendly.) What's interesting is that this scenario wasn't caused by any kind of deadlock; it was caused by a sleeping connection implicitly holding some kind of lock that prevented the DROP TRIGGER statement from executing, just by virtue of having done a SELECT on the same table previously. None of the anti-deadlock features of MySQL could automatically kill a process and save the situation, because ultimately everything could continue as soon as my original process - the idle one that had only ever done SELECTs - was killed. The fact that MySQL locks behave this way by default seems perverse to me, but that's not the point. I'm trying to figure out a way to ensure that the disaster scenario described above can't ever recur (especially on our live server). How would you suggest I do this? We've talked the problem over in the office, and there are a couple of hypothetical solutions we saw: Change some config setting somewhere so that sleeping processes time out after 10 seconds by default, so that a sleeping process can never sit on locks. Better yet, have them just release all locks after 10 seconds so that I can still go to lunch and leave my MySQL shell open, or my Python window open with a MySQLdb connection active, then come back and use it, without fear of breaking anything. This might be really irritating when trying to run queries manually, especially ones that require grouping into a transaction. Work some magic on the queries that try to replace the triggers and stored procedures so that the acquisition of locks required for the relevant DROPs and CREATEs is made into an atomic operation - something like, if the query can't acquire all the locks it needs immediately in sequence, then it releases them and tries again periodically until it works. This might just make our deployment process never complete, though, if the database is too busy for it to be able to grab all the locks in one go. Drastically reduce the frequency of schema-modifying queries we make (it only seems to be these that can be blocked from starting by a connection that's only done SELECTs), for instance by having our deployment script check whether a stored procedure or trigger in source control has changed from the version in the database before DROPping and reCREATEing the one on the database. This only mitigates the problem, it doesn't actually eliminate it. We're not sure if either of the first two solutions we considered are even possible in MySQL, though, or if we're missing a better solution (we're developers, not DBAs, and this is outside of our comfort zone). What would you recommend?

Bitmask Flags with Lookup Tables Clarification Posted: 29 Aug 2013 08:17 PM PDT I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries based on these fields using AND or OR logic. Using a calculated field, where I just convert the bits into an integer value, I can easily find rows that match an AND query, by using a simple WHERE rowvalue = requestvalue, but the OR logic would require using bitwise & in order to find matching records. Given that I would need to work with several of these columns and select from hundreds of millions of records, I feel that there would be a huge performance hit when doing bitwise & operations to filter my SELECT results. I came across this answer from searching and it looked like it may fit my needs, but I need some clarification on how it is implemented. Is this as simple as creating a lookup table that has all possible search conditions? Example for 3 bits using (a & b) (Edit: Wrong bitwise op) 001,001 001,011 001,101 001,111 010,010 010,011 010,110 011,011 011,111 etc The author mentions that it's counter-intuitive initially, but I can't help but feel I'm interpreting the solution incorrectly, as this would give me a single lookup table with likely billions of rows. Any clarifications on the answer I linked above or other suggestions that would preserve the existing database are appreciated. Edit: A more concrete example using small data. Four flags, HasHouse,HasCar,HasCat,HasDog, 0000 is has none, 1111 is has all. Any number of flags, from all to none, can be flipped, and results must be filtered where selection matches all (Using exact value comparison) or at least 1 (Using bitwise &). Adding a single calculated column for each bitmask is ok, but adding a column for each bit for more than 100 bits, coupled with how to insert/update the data is why I'm trying to find alternative solutions.

SQL Server 2012 catalog.executions to sysjobhistory - any way to join them? Posted: 29 Aug 2013 03:17 PM PDT I have exhausted my resources and can't find a foolproof way to join the ssisdb.catalog tables to the jobs that run them. Trying to write some custom sprocs to monitor my execution times and rows written from the catalog tables, and it would be greatly beneficial to be able to tie them together with the calling job.

SQLite writing a query where you select only rows nearest to the hour Posted: 29 Aug 2013 05:17 PM PDT I've got a set of data where data has been taken approximately every minute for about three month and the time has been stored as a unix timestamp. There is no regularity to the timestamp (i.e. the zero minute of the hour may not contain a reading, 00:59:55 and the next measurement could be 01:01:01) and days may be missing. What I need is the row nearest to the hour, with the timestep rounding to the hour, as long as the nearest value is not more than 30 minutes away from the hour. Where a matching hour could not be found it would be helpful if the query could include a time but no value. I realise I'm asking a lot, but this would be incredibly helpful Thanks for taking the time to read this. James BTW, The table is just PK (autoincrement),timestamp,value, sensor id(FK). I've tried this to get the data out: SELECT strftime('%S',time, 'unixepoch'),strftime('%M',time, 'unixepoch'),strftime('%H',time, 'unixepoch'), strftime('%d',time, 'unixepoch'), strftime('%m',time, 'unixepoch'), strftime('%Y',time, 'unixepoch'), value from Timestream where idSensor=359;

Listing the existing SQL Server Logins and Users Posted: 29 Aug 2013 01:37 PM PDT I know we can check the logins and the users that are defined, using GUI in SQL Server, but am wondering how we can do this check using script. I ran the query below but it shows Principal_id which I'm not sure how to map to get the permission level. SELECT * FROM Sys.login_token So is there any built-in stored proc that can list the logins and the users with their permission level? Thank you.

slow load speed of data from mysqldump Posted: 29 Aug 2013 11:44 AM PDT I've got a moderate size MySQL database with about 30 tables, some of which are 10 million records, some 100 million. The mysqldump of all the tables (into separate files) is fairly fast, takes maybe 20 minutes. It generates about 15GB of data. The largest dumped files are in the 2GB range. When I load the data into MySQL on another box, a six-core, 8GB machine, it takes forever. Easily 12 clock hours or more. I'm just running the mysql client to load the file, i.e. mysql database < footable.sql directly with the file directly out of mysqldump mysqldump database foo > footable.sql Clearly I am doing something wrong. Where do I start so it can finish in a reasonable time? I'm not using any switches on either the dump or the load.

Add Oracle Label Security to an existing Oracle installation Posted: 29 Aug 2013 01:45 PM PDT We are using Amazon EC2 to host some Oracle database instances while we are evaluating and prototyping some software. We are using the AMIs provided by Oracle - specifically, Oracle Database 11g Release 2 (11.2.0.1) Enterprise Edition - 64 Bit (Linux). I now need to do some work based on Oracle Label Security - OLS. It appears that when Oracle was first installed into an AMI, the Oracle Label Security option was not enabled. So I want to add OLS to an existing installation of Oracle. I have tried following the installation instructions in the Oracle Label Security Administrator's Guide, using media downloaded from following the download links on the Oracle home page. However the instructions don't match up with what I see when I run the installer - according to the instructions, on the second page of the wizard I should see an Advanced Installation option, but I don't see that - I can only see three radio buttons, "Create and configure a database", "Install database software only" and "Upgrade an existing database". I tried to muddle through the instructions but there were several inconsistencies, as if I was running a different version of the installer to the documentation writer. The AMI, documentation and media are all Oracle Database 11g Release 2 (11.2.0.1) so I don't know why they don't all seem to match. I tried to proceed with an installation anyway, and did find the checkbox to install Oracle Label Security. However it would not accept my existing ORACLE_HOME as an installation destination, telling me: [INS-32025] The chosen installation conflicts with software already installed in the given Oracle home. So, in short - is it possible for me to add the OLS option to an existing installation? If so - how? If not - how do I make a new installation and attach the existing database to the new installation? Edit 2011-05-23 After doing some more poking around, I found a copy of the Oracle installer at $ORACLE_HOME/oui/bin/runInstaller. This installer looks visually different and has different page flows. First thing it wanted to know was the source location, so I gave it the path to stage/products.xml on the installation media. It then gave me a choice of Enterprise, Standard or Custom - I selected Custom. It then asked for the Oracle home - this was pre-selected with the value of $ORACLE_HOME. So far so good. Then, the first odd thing: it gave me a long list of installable products. Oracle Label Security was in this list, but it was already marked as "Installed". However if I clicked the Installed Products... button for information, Oracle Label Security was not present. I selected Oracle Label Security anyway and it changed to say "reinstall". But on clicking Next I got this error:

How can I track database dependencies? Posted: 29 Aug 2013 04:32 PM PDT As internal applications evolve over a number of years, you occasionally find there are a number of tables that people believe are no longer relevant and want to cull. What are the practical methods for identifying database dependencies, both within the SQL environment, and maybe onward into things like SSIS? I've worked places where fairly brutal options have been taken such as: Drop first, ask questions later (can kill a data warehouse build if it tries to extract a table that no longer exists) Remove permissions first, and wait for the errors to be reported (can cause silent bugs, if the failiure isn't handled correctly) I appreciate that SQL Server comes with tools for tracking dependencies within that instance, but these seem to struggle if you have databases on different instances. Are there options that make it easier to query dependencies, maybe answering questions like "Where is this column used?" with answers like "Over on this other server in this stored procedure" or "Over in this SSIS package"?

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