[SQL Server 2008 issues] DatabaseOptimization - job failed

---

• DatabaseOptimization - job failed
• Update the column with names
• Replace NUll values with Space in SSIS 20008
• Can a table have two primary keys?
• Tool to search, list out distinct .sql files
• GETDATE() returns null in production
• Excel Source unable to read alphanumeric values.
• Replication DB migration on new sql server 2008R2
• Merge Replication merging thousands of records each time
• Indexing a view
• SSRS Only Returning First Row
• Intermittent pre-login handshake error
• SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR
• password
• Managing duplicated code?
• using in with SP
• Port numbers 1433 and 1434
• Cross Tab or Pivot - Need Help
• How to Compress files in a folder
• Null Value column names
• Replication topology question
• SP Output Parameter Issue (across ODBC Connection)
• SQLServer Error: 15404
• Sql cluster8 r2 with out msdtc

DatabaseOptimization - job failed Posted: 11 Aug 2013 08:00 PM PDT My Optimization job failed with below error.Edition: Enterprise Edition (64-bit) Procedure: [master].[dbo].[IndexOptimize] Parameters: @Databases = 'USER_DATABASES', @FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE', @FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE', @FragmentationMedium_LOB = 'INDEX_REORGANIZE', @FragmentationMedium_NonLOB = 'INDEX_REORGANIZE', @FragmentationLow_LOB = 'NOTHING', @FragmentationLow_NonLOB = 'NOTHING', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @LOBCompaction = 'Y', @StatisticsSample = NULLDateTime: 2013-08-11 01:00:00 Database: [allmed] Status: ONLINE Updateability: READ_WRITEDateTime: 2013-08-11 01:01:15 Database: [archsys] Status: ONLINE Updateability: READ_WRITEDateTime: 2013-08-11 01:01:48 Database: [cs3live] Status: ONLINE Updateability: READ_WRITEDateTime: 2013-08-11 01:02:10 Command: ALTER INDEX [i_1985510508x0] ON [cs3live].[scheme].[slxrefm] REORGANIZE WITH (LOB_COMPACTION = ON) Comment: IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 2763, Fragmentation: 5.13934Outcome: Succeeded Duration: 00:00:02 DateTime: 2013-08-11 01:02:12DateTime: 2013-08-11 01:04:38 Command: ALTER INDEX [i_1764282674x2] ON [cs3live].[scheme].[opheadm] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 3282, Fragmentation: 92.8702Msg 50000, Level 16, State 1, Server UKDXSDBCVTC016, Procedure CommandExecute, Line 95 Msg 1222, Lock request time out period exceeded.Outcome: Failed Duration: 01:00:12 DateTime: 2013-08-11 02:04:50DateTime: 2013-08-11 02:04:55 Command: ALTER INDEX [i_1764282674x4] ON [cs3live].[scheme].[opheadm] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 3238, Fragmentation: 92.0012Msg 50000, Level 16, State 1, Server UKDXSDBCVTC016, Procedure CommandExecute, Line 95 Msg 1222, Lock request time out period exceeded.Outcome: Failed Duration: 01:00:05 DateTime: 2013-08-11 03:05:00DateTime: 2013-08-11 03:05:01 Command: ALTER INDEX [i_1764282674x6] ON [cs3live].[scheme].[opheadm] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 3928, Fragmentation: 62.4745Msg 50000, Level 16, State 1, Server UKDXSDBCVTC016, Procedure CommandExecute, Line 95 Msg 1222, Lock request time out period exceeded.Outcome: Failed Duration: 01:00:20 DateTime: 2013-08-11 04:05:21DateTime: 2013-08-11 04:05:21 Command: ALTER INDEX [i_1764282674x8] ON [cs3live].[scheme].[opheadm] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 1923, Fragmentation: 85.5434Msg 50000, Level 16, State 1, Server UKDXSDBCVTC016, Procedure CommandExecute, Line 95 Msg 1222, Lock request time out period exceeded.Outcome: Failed Duration: 01:00:03 DateTime: 2013-08-11 05:05:24DateTime: 2013-08-11 05:05:25 Command: ALTER INDEX [i_1764282674x12] ON [cs3live].[scheme].[opheadm] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 2494, Fragmentation: 57.5782Msg 50000, Level 16, State 1, Server UKDXSDBCVTC016, Procedure CommandExecute, Line 95 Msg 1222, Lock request time out period exceeded.Outcome: Failed Duration: 01:00:04 DateTime: 2013-08-11 06:05:29DateTime: 2013-08-11 06:05:29 Command: ALTER INDEX [i_6928d132002dd31e] ON [cs3live].[scheme].[opheadm] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 6266, Fragmentation: 43.2333Msg 50000, Level 16, State 1, Server UKDXSDBCVTC016, Procedure CommandExecute, Line 95 Msg 1222, Lock request time out period exceeded.Outcome: Failed Duration: 01:00:04 DateTime: 2013-08... Process Exit Code 1. The step failed.Please help me to rectify it.advance thanks

Update the column with names Posted: 12 Aug 2013 05:14 PM PDT I have a table with column and data is [u]blp_proposalno blp_documentattach blp_fund blp_branch[/u]0002755746 201,202,203,204,211 116 KA010002997284 201,202,203,204,214,211 116 KA010003234742 201,202,203,204,211 116 KA010004046205 201,202,203,204,211 116 JH730003659836 201,202,203,204,211 116 JH730004029283 201,202,203,204,211 116 JH730002975418 201,202,203,204,214 116 AL1390003176467 201,202,203,204,211 116 AL170003365092 201,202,203,204 116 BT010003450570 201,202,203,204 116 AS640004056265 201,202,203,204,214 116 JM420007359820 NULL 116 JA070007415964 NULL 116 NO130007244229 NULL 116 AL280007490863 NULL 116 MO06And the other table with data is [b][u]BLD_Fund BLD_DocumentID BLD_Document[/u][/b]116 201 Benefit Illustration116 202 Age proof116 203 Address Proof116 204 Proof of Identity116 205 Income proofs116 206 Risk Appetite awareness form116 207 ECS Mandate116 208 CC Mandate116 200 Application Form116 209 Employee Documents116 210 Occupation Related Documents116 211 Cash Authority Documents116 215 Single Name Documentation116 212 NRI Documents116 213 PEP Documents116 214 Female Life Guidelinesso i need the first table column update like the given below..[b][u]bld_Document attach[/u][/b]Benefit Illustration,Age proof,Address Proof,Proof of Identity,Cash Authority DocumentsBenefit Illustration,Age proof,Address Proof,Proof of Identity,Female Life Guidelines,Cash Authority Documentsand so on....Help me...

Replace NUll values with Space in SSIS 20008 Posted: 12 Aug 2013 07:29 AM PDT HI, I am dumping data from source to destination but ..problem is some values in source table have null values but in the destination table, it does'nt allow null values. So for those values, i need to put space for those values.. Any idea how to do it and where to put it.regardsshaun...

Can a table have two primary keys? Posted: 06 Aug 2012 07:47 PM PDT Can a table have two primary keys

Tool to search, list out distinct .sql files Posted: 06 Aug 2013 05:31 AM PDT Is there any tool which can scan through each script and spit out list of scripts which are same?

GETDATE() returns null in production Posted: 12 Aug 2013 01:43 AM PDT I have an Access application as a front-end to an SQL Server 2008 backend. There is one field (dtDateEmission) on one table that has a default value of (getdate()). This is on the SQL Server side when I look at the table's properties. When I manually enter a table directly using SSMS the default works and the field gets the current data and time.In production, the Access application inserts a record in this table by calling a stored procedure. The stored procedure does not reference the field dtDateEmission, only other fields are given values and the record gets added to the table. Yet, when this is happenning, the dtDateEmission field is given a value of NULL. I traced the execution using SQL Profiler and there is nowhere in the application that assigns a null value to this field. Yet when the user clicks the button in the app to enter a new record, the field gets a NULL value, even with a default of GETDATE() at the table level.I copied the database over to our development environment, and I ran the application myself. With me, the field gets the current date and time and everything works. But in production, the user ends up saving a NULL value for this field.Very strange!Can anyone explain this or has something like this happen to them?Thanks!

Excel Source unable to read alphanumeric values. Posted: 12 Aug 2013 07:19 AM PDT I'm creating a simple source--Destination Excel load from a an excel file to SQL server Table using SSISOne of the column getting nulls where the data is alphanumeric. Example:Source DestinationCol A -----> Col A nvarchar(255)12321 ------> 123211D2DE34 -----> NULL -- Here is the issue I have tried loading it to a flat file destination and the it is sending null values where there is alphanumeric, from this I understood there is no issue on SQL server side but the excel source was not reading the alpha numeric values from the Excel. The excel column format is TEXTDo anyone have seen this issue before, ? Please advise.

Replication DB migration on new sql server 2008R2 Posted: 08 Aug 2013 07:49 AM PDT Hello,We will be planning to migrate sql 2005 and sql 2008R2 on new windows 2008R2 server, we have one Replicated DB which ones we are subscribing and we don't have any control over publisher.Could you please guide me that how can we handle it?We are planning to do side by side installation and will be using Backup and Restore method but some worries how we can handle it for Replication DB and which is the biggest DB almost more then 300GB.Thanks,

Merge Replication merging thousands of records each time Posted: 12 Aug 2013 09:31 AM PDT I have SQL 2008 Standard on two servers. Testing merge replication between the servers on two tables. 1. Both tables have about 500k records. 2. These servers are not in use currently, just being tested for replication, so there is very little if any changes to the data.3. Set up and initialization seem fine. 4. The databases were identical when we started. Tried several methods, attach/detach, backup, etc...5. When the Merge Agent runs the first time, there are 150,000 updated records. Next time, 42,810 records updated and 42,800 conflicts. Third time, is running now. Looks like 200k plus updates and conflicts. Taking about 90 minutes.I first noticed this because the log file was getting huge when i had the timing on continuous. Now I have it on manual so i can track what is happening.Any ideas what may be happening ?Thanks in advance.

Indexing a view Posted: 12 Aug 2013 05:20 AM PDT I'm using a SQL view to retrieve data from 8 different tables without having to do 8 select statements.This has sped my project up quite a bit over the 8 selects.But I still have some users complaining that it is slow.Is it possible to index a view to improve its performance?

SSRS Only Returning First Row Posted: 12 Aug 2013 04:24 AM PDT Hi all,I am trying to get SSRS to display unique data for a unique ID. It's fairly straightforward, and I've done it before. But for some reason now it doesn't work. For example, I have an id which the user can choose, and a textbox that displays the name for that id. It should change with each different id, as should the other columns in order to display customized data, but they don't -the data matches the data returned from the first row and first row only. Any help or advice?

Intermittent pre-login handshake error Posted: 05 Aug 2013 03:24 PM PDT Environment:SQL 2008 R2 ClusterRunning fine for over 6 monthsEvery once in a while I get some time-outs from our web apps with a SQLAsync Exception:Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1; handshake=15003; Anyone get this before? Again the server is listening on port 1433 and the connection strings are using TCP\IP. This is a intermittent error. I remember something like this that had to do with multiple instances and Named Pipes and TCP or the client is trying to connect via TCP\IP v6 first then 4 but I have checked all that and it seems fine.thanks in advance

SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR Posted: 04 Aug 2013 12:26 PM PDT If I set the SSIS package to ignore the error this column pulls in null values instead of real values. If it's not set to ignore the error this is the error message. The data type is INT. It's failing in the ADO NET Source task. Any ideas? [ADO NET Source [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "ADO NET Source" (1)" failed because error code 0x80131937 occurred, and the error row disposition on "output column "XYZ" (749)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

password Posted: 12 Aug 2013 01:35 AM PDT How to the retrieve the password for SQL login, if forgot the password?

Managing duplicated code? Posted: 12 Aug 2013 03:01 AM PDT I have some jobs running a few scripts that populate several reports. I've run these reports reports two ways:1. Each report has their own procedure (around 25 procedures).2. All of the reports share a few procedures (around 5 or 6).I'm actually finding it optimal to run them from their own procedure. (This particular project will not get any bigger). However, in many of the same places the code is basically the same. Is there a good (or easy) way to keep up with the general source code (I'm not sure if that is the right term). Basically, I'm hoping for an easier way to manage updates to the general code that is duplicated across the procedures. Any help is appreciated.

using in with SP Posted: 11 Aug 2013 09:59 PM PDT i have a SP which has a param id as intand the where claus ein SP had where id=@IDbut now instead of a single vlaue i get id as multiple valuesso i need IN operatorredefined ID as varchari used where id IN (@ID) and when executing SP as EXEC usp_itemdeatils '45,72' i get error Conversion failed when converting the varchar value '45,72' to data type int.

Port numbers 1433 and 1434 Posted: 07 Aug 2012 04:05 AM PDT Hi DBA'swhy there are two ports 1433 and 1434 for SQL Server.whts the purpose of two?how are they different?I came to know 1433 is for TCP\IP1434 for UDP what does this mean ?Thanks in advance!

Cross Tab or Pivot - Need Help Posted: 12 Aug 2013 01:06 AM PDT I have the following tables.TabParent========id item1 item2-- ------ ------AA 2 2TabChild======id f1 f2 f3 f4 f5-- -- -- -- -- --AA 1 2 3 4 1AA 1 1 1 2 2AA 1 1 1 3 3Now what I want is (i.e each row from child becomes appended as column sets in single row)-----------------------------------------------------------------------------------------id item1 item2 f11 f12 f13 f14 f15 f21 f22 f23 f24 f25 f31 f32 f33 f34 f35-- ------ ------ --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---AA 2 2 1 2 3 4 1 1 1 2 2 2 1 1 1 3 3Please let me know,1. Is it possible to do?2. If yes how a static as well as dynamic (if no of rows in child table for a key increases) solutions could be implemented through T-SQL code?Thanks and Advance

How to Compress files in a folder Posted: 12 Aug 2013 12:05 AM PDT I have a folder in D:\Payslip with a set of files.I have a table which stores the file name. Based on stored procedure inputs i need to create a zip file with the file names based on table in the same folder. Please suggest which command can be used without using tools like winrar, 7zip, winzip

Null Value column names Posted: 11 Aug 2013 07:53 PM PDT hi Team,Am having a table with 100 columns, and 500 records,in that some records values are NULL, i want to find out how many column are there with NULL records.i want all NULL value column names.eg :col1 col2 col3a NULL cityb YOK BHKc LOK BTLOutputcol2, col1, and col3 contains no NULL values, only col2 contains NULL, hence i want col2 as output. Please help...

Replication topology question Posted: 11 Aug 2013 10:23 PM PDT HiI'm planning to add a couple of new SQL 2012 subscribers to a transactional replication setup that has a 2008R2 publisher/distributor and several subscribers that include two SQL 2000 machines. According to [url=http://msdn.microsoft.com/en-us/library/ms143241%28v=sql.105%29.aspx]this link[/url], that should work as[quote]For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version.[/quote]However, I was just wondering if anyone had actually tried having 2000 and 2012 subscribers in the same replication topology?Duncan

SP Output Parameter Issue (across ODBC Connection) Posted: 11 Aug 2013 08:04 PM PDT Hello,I am having an issue retrieving a SP's Output parameters over an ODBC connection (Driver: SQL Server Native Client 10).When the SP is not attempting any inserts/deletions or updates, the Output params are returned across the ODBC connection fine. However, as soon as I add in a simple update statement (updating one field in one record), it is suddenly unable to retrieve the Output params.I have tried this on other SPs (with different update statements) and the same is happening? [u]HOWEVER[/u], when I capture the ODBC call to the SP in 'SQL Server Profiler' and run in the Management Studio then it returns the Output params fine which is why I am thinking it is an ODBC issue/restriction? All accounts used are SysAdmin so cannot see any permission issues? I have also removed all triggers on the tables being updated incase these were interfering at all?Will paste the SP script/call if required, but any pointers first would be greatly appreciated, :-)

SQLServer Error: 15404 Posted: 06 Aug 2013 07:34 PM PDT It appears to me that SQL Server do not support hostname over 8 characters. When I enter my hostname to schedule an SQL maintenance plan and save it, it will automatically rename my account from: SOLOMON2009\Administrator to SOLOMON2\Administrator. Backup will fail with the below error log:[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'SOLOMON2\Administrator', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

Sql cluster8 r2 with out msdtc Posted: 06 Aug 2013 10:07 PM PDT One of our client want dba to install sql cluster 2008 r2 on single cluster node.(maybe in the future a second node will be add to the cluster).Can any one clarify the below things.IS it possible to configure msdtc with out secodary node.? Is it possible to install sql cluster on 2008R2 on sinlge node with out configuring MSDTC? orcan we create MSDTC after adding the 2nd node to the existing single node cluster?Advance thanks,

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] Can Mysql Cluster AQL(Push-Down Join) be trigger with sharding table at the same time?

---

• Can Mysql Cluster AQL(Push-Down Join) be trigger with sharding table at the same time?
• How to create Combination of records (Order does not matter, no repetition allowed) in mySQL tables
• How to partition an existing non-partitioned table
• Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL Server
• Updating a big replicated Dimension (SQL Server PDW)
• having trouble using dbms_scheduler
• Are passwordless logins like ##MS_SQLResourceSigningCertificate## , ##MS_AgentSigningCertificate## a vulnerability?
• PostgreSQL postmaster will not start after configuring for SymmetricDS
• Create a new database in SQL Server 2008 R2, but it is not Blank
• Mathematical method for spotting irregularity in numbers flow
• SQL Server insert randomly takes 10+ seconds to complete?
• Defining a process for troubleshooting performance issues [on hold]
• is there a recommended way to update many postgres schemes
• ORA-01031 while creating a view as DBA
• Percona mysql server slow queries
• Possible to run multiple Reporting Services (SSRS) instances on different machines with databases on same machine?
• Oracle won't start
• MariaDB Galera Cluster Replication error, no state received?
• Error: "Storage Engine for the Table Doesn't Support Nullable Columns" (SequelPro)
• optimizing MySQL for traffic analytics system
• LDAP in SQL Server 2012
• Primary replica set server goes secondary after secondary fails
• MySQL backup InnoDB
• Strange characters in mysqlbinlog output
• Connecting to a SQL Server database from a Flash program
• Oracle Express edition on Ubuntu - control file missing
• Is using multiple foreign keys separated by commas wrong, and if so, why?

Can Mysql Cluster AQL(Push-Down Join) be trigger with sharding table at the same time? Posted: 12 Aug 2013 06:45 PM PDT From the jonas slide, I knew that the feature of "Push-Down Join" can make join 70x faster. the most important factor is to eliminate the round trip between API node and data nodes. where all data in the same node, it is not necessary to do inter-node communication. But what if a certain table is partitioned to some group of data nodes, how AQL eliminate the data shipping? can push-down work in sharding scene?

How to create Combination of records (Order does not matter, no repetition allowed) in mySQL tables Posted: 12 Aug 2013 08:13 PM PDT I've got a table that has hundreds of rows, each row is a recipe with nutritional information, for example: recipe_table: id | calories | protein| carbs | fat recipe1, 100, 20g, 10g, 2g recipe2, 110, 10g, 12g, 12g recipe3, 240, 20g, 1g, 23g .... I needed to create a new table (recipe_index) that would show every possible combination of every recipe in recipe_table as a set of 3, so it would look something like: recipe_index: id1 | id2 | id3 |calories| protein | carbs | fat recipe1, recipe2, recipe3, 450, 50g, 23g, 37g .... Basically it allows me to query recipe_index and say "what 3 recipe combinations come to a total value that's between 440 calories and 460 calories" My current code for doing this works at 3 meals, however I end up with about 450,000 records in recipe_index, I need to do this same thing for 4,5 and 6 meals as well, so I'm calculating billions of records at the end of this. Is there a more efficient way of doing this? Perhaps I need to look into partitioning a table for each range? My current SQL code: INSERT INTO recipe_index SELECT distinct '3' as nummeals, t1.id as id1, t2.id as id2, t3.id as id3, 0 as id4, t1.calories_ps+t2.calories_ps+t3.calories_ps as calories, t1.protein_ps+t2.protein_ps+t3.protein_ps as protein, t1.carbohydrate_ps+t2.carbohydrate_ps+t3.carbohydrate_ps as carbohydrate, t1.fat_ps+t2.fat_ps+t3.fat_ps as fat from recipes t1 inner join recipes t2 on t1.Id < t2.Id inner join recipes t3 on t2.Id < t3.Id WHERE t1.image <> '' AND t2.image <> '' AND t3.image <> ''

How to partition an existing non-partitioned table Posted: 12 Aug 2013 08:01 PM PDT I have an existing table with data: dbo.Test (col1,col2,col3....) ON [PRIMARY] I need to change this table to be partitioned like this: dbo.Test(col1,col2,col3....) ON Ps_Date(Col2) How I can I achieve this without dropping and recreating the table?

Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL Server Posted: 12 Aug 2013 04:06 PM PDT I just found that on the SQL Server 2012 MSDN documentation for binary and varbinary types, they mention the following at the end of the article: Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL Server. I noticed that this does not exists in the previous versions of the article for 2005, 2008, and 2008 R2. I searched around online but didn't find any exact explanations that satisfied my question of "WHY?". (Which is the question I have here.) The project I am working on needs to store data as varbinary(max), but based on this little note, I am afraid if I need to move the binary data and then convert it on another SQL Server version that it will not match the original data converted to binary, so answering this question more in depth would be a great help. Thanks!

Updating a big replicated Dimension (SQL Server PDW) Posted: 12 Aug 2013 04:10 PM PDT We use a SQL Server PDW appliance for our data warehouse. One of the tables in our warehouse is a replicated table with about 20 million rows. As part of our ETL process we need to expire old records from this dimension; however, we are seeing that updating a handful of records (<100) takes over 1 hour to complete. This is what I would like to improve if I can. Naturally, one option that I thought about was changing this Dimension from Replicated to Distributed. My testing shows that it would fix the issue with the ETL process taking long (from 1.5 hours came down to 30 secs) but all the joins against the Distributed version of this dimension would be affected since the joins are almost never based on the same distribution column. When I look at the execution plan of some of these queries I usually see either a ShuffleMove or a BroadcastMove operation. So my question to the PDW guru's here is: Is there anything else that can be done in order to improve the performance of updating records in the replicated version of this Dimension? Again, moving to a Distributed table doesn't seem to be the best solution since it will affect hundreds of already written SQL queries and reports developed by other people.

having trouble using dbms_scheduler Posted: 12 Aug 2013 01:40 PM PDT I am using oracle 11g and have never used dbms_scheduler jobs. I have this query that I want to update every day at 5. BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'UPDATE_VIEW_ACCTUSE' ,start_date => trunc(sysdate) + 8/24 ,repeat_interval => 'freq=MINUTELY;interval=2' ,end_date => trunc(sysdate) + 20/24 ,job_class => 'DEFAULT_JOB_CLASS' ,job_action => 'declare in_clause varchar2(2560); sel_query varchar2(2560); n number := 0; begin for x in (select distinct userfieldcd from acctuserfield) loop if n <> 0 then in_clause := in_clause || ', '; end if; in_clause := in_clause || '''' || x.userfieldcd || ''''; n := 1; end loop; sel_query := 'select * from (select Acctnbr, userfieldcd, value from acctuserfield) pivot (max(value) for userfieldcd in ('||in_clause||'))'; dbms_output.put_line (sel_query); execute immediate 'create or replace view VIEW_ACCTUSE as ' ||sel_query; end;' ,comments => NULL ); END I put my query in the job_action. Is that how you do it?

Are passwordless logins like ##MS_SQLResourceSigningCertificate## , ##MS_AgentSigningCertificate## a vulnerability? Posted: 12 Aug 2013 11:39 AM PDT I'm new to MS SQL Server. Colleagues working in IT security, have run an scan showing come DB users with null password. Some of them are ( # included ) ##MS_SQLResourceSigningCertificate## ##MS_SQLReplicationSigningCertificate## ##MS_SQLAuthenticatorCertificate## ##MS_PolicySigningCertificate## ##MS_SmoExtendedSigningCertificate## ##MS_AgentSigningCertificate## I suspect they are not a security threat but, since they don't use Windows Authentication either, I don't know how to support that assumption. What are these logins for ?

PostgreSQL postmaster will not start after configuring for SymmetricDS Posted: 12 Aug 2013 11:45 AM PDT I'm trying to configure SymmetricDS for use with PostgreSQL, a database platform widely supported by the former. When running through the quick-start tutorial (which leads you to some pre-setup documentation), I am instructed to append the following line to postgresql.conf: custom_variable_classes = 'symmetric' The next step of the tutorial requires the postmaster to be running (at least that's what I glean from the stack trace...), so I service postgresql-9.2 start but this fails. Without this single line added (or if it's commented out), PostgreSQL starts as expected. I must be doing something wrong, so what is it? yum install postgresql92 postgresql92-server Download and unzip SymmetricDS into ~ service postgresql-9.2 initdb Edit /var/lib/pgsql/9.2/data/postgresql.conf as above

Create a new database in SQL Server 2008 R2, but it is not Blank Posted: 12 Aug 2013 01:06 PM PDT When I try to create new database in SQL Server 2008 R2, it was created completely, but it is not a blank and empty database, there are many tables that come from another database. I also tried specifying a different name for its .mdf and .ldf file, but nothing changed! Would you please help me to know where is the problem? Also, is there any command that would reset my database as a blank db?

Mathematical method for spotting irregularity in numbers flow Posted: 12 Aug 2013 01:08 PM PDT I am trying to build an engine which is going to predict orders for items inside shop. Three main variables are: Time-dates (x-axe etc 1.1.2013,1.2.2013 etc I do calculation one per day) Available amount of an item. (green line inside graphs) This line represend available amount of an item. And as item being sold it decreases. Dependent on new order (time until new order. Purple line on graph) If we making an order every monday this line is going to be 0 at monday and 7 at satutday.It is not always 7 days base some time it is 7 some time is 14 etc depending on supplier. It is reset with every time when supplier bring items to warehause. In next three graphs I'll show three possible situation which I am hunting. 1.) Ideal situation, I sold out my items just before my next order is coming . I want to react on 9th day (blue) to send new order to supplier 2.) Bad situation when I sold faster when my order is coming. I want to react at 3th (orange) day . So I can warn user that he is sold items fast. Bad situation Items are going out too slow and at end of order cycle I will have overbalance items on stock. Here is sample data of (MSSSQL) CREATE TABLE #stock( id int NOT NULL, item_id int not null, ts datetime not null, amount float not null) create table #orders ( id int not null ,item_id int not null ,ts datetime not null ,days_until_next int not null) create table #items (id int not null ,name varchar(50)) insert into #items select 1,'Good art' union all select 2,'Fast art' union all select 3,'Slow art' insert into #orders select 1,1,'2013-01-01',8 union all select 2,2,'2013-01-01',8 union all select 3,3,'2013-01-01',8 insert into #stock select 1,1,'2013-01-01',8 union all select 2,1,'2013-01-02',7 union all select 3,1,'2013-01-03',6 union all select 4,1,'2013-01-04',5 union all select 5,1,'2013-01-05',4 union all select 6,1,'2013-01-06',3 union all select 7,1,'2013-01-07',2 union all select 8,2,'2013-01-01',200 union all select 9,2,'2013-01-02',150 union all select 10,2,'2013-01-03',100 union all select 11,2,'2013-01-04',50 union all select 12,2,'2013-01-05',0 union all select 13,2,'2013-01-06',0 union all select 14,2,'2013-01-07',0 union all select 15,3,'2013-01-01',100 union all select 16,3,'2013-01-02',80 union all select 17,3,'2013-01-03',70 union all select 18,3,'2013-01-04',70 union all select 19,3,'2013-01-05',60 union all select 20,3,'2013-01-06',60 union all select 21,3,'2013-01-07',50 select * from #items select * from #orders select * from #stock I want to construct a query which is going to be run every day and try to find these situations.

SQL Server insert randomly takes 10+ seconds to complete? Posted: 12 Aug 2013 10:48 AM PDT I have an insert statement that is being generated by the .NET Entity Framework. In most cases, this particular insert will execute in 0ms according SQL Server Profiler. One out of every 30 or so inserts will jump to as high as 12 seconds duration, causing the .NET client on the other end to show as "Not Responding" while it waits. Server load should not be an issue as our server is very, very lightly loaded. Here is the table the insert is being performed against: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[ProductEvents]( [EventID] [int] IDENTITY(1,1) NOT NULL, [KID] [int] NOT NULL, [EventDescription] [varchar](50) NOT NULL, [EventDate] [datetime] NOT NULL, [UserName] [varchar](50) NOT NULL, [Notes] [varchar](max) NOT NULL, [Version] [timestamp] NOT NULL, [IsSynchronized] [bit] NOT NULL, [LastSyncDate] [datetime] NULL, CONSTRAINT [PK_ProductEvents] PRIMARY KEY CLUSTERED ([EventID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[ProductEvents] ADD CONSTRAINT [DF_ProductEvents_IsSychronized] DEFAULT ((0)) FOR [IsSynchronized] GO ALTER TABLE [dbo].[ProductEvents] WITH CHECK ADD CONSTRAINT [FK_ProductEvents_Products] FOREIGN KEY([KID]) REFERENCES [dbo].[Products] ([KID]) ON DELETE CASCADE GO ALTER TABLE [dbo].[ProductEvents] CHECK CONSTRAINT [FK_ProductEvents_Products] GO And the query as seen by SQL Server Profiler (actual example): exec sp_executesql N'insert [dbo].[ProductEvents]([KID], [EventDescription], [EventDate], [UserName], [Notes], [IsSynchronized], [LastSyncDate]) values (@0, @1, @2, @3, @4, @5, null) select [EventID], [Version] from [dbo].[ProductEvents] where @@ROWCOUNT > 0 and [EventID] = scope_identity()',N'@0 int,@1 varchar(50),@2 datetime2(7),@3 varchar(50),@4 varchar(max) ,@5 bit',@0=1894,@1='Modified',@2='2013-08-12 08:09:25.4766233',@3='KNEXTION\aellison', @4='Description changed from Mini Awareness Ribbon Cookie Cutter - RM 1698 to Mini Awareness Ribbon Cookie Cutter - R&M 1698.',@5=0 I will also be glad to post the Query Plan, but I'm not sure in what format I should post it (I'm mainly a StackOverflow guy). EDIT: Here is the execution plan as a screenshot from SSMS. Any ideas on how to begin to track this down?

Defining a process for troubleshooting performance issues [on hold] Posted: 12 Aug 2013 11:50 AM PDT The issue of poor database performance is one which presents itself from time to time and there are many techniques which can be employed to examine the root cause of slowness. Does anyone have a clearly defined process which they use to troubleshoot performance, like a tried and tested 'works for me' method for investigating issues? For me I might employ the following (not necessarily in this order) check if data index/statistics maintenance ran successfully (if possible/appropriate re-run if failed) examine fragmentation of indexes (rebuild/re-organ depending on frag amount) examine the last time statistics were updated (maybe update stats depending on age and whether a large number of data changes had taken place) check if there's any blocking (kill off the offending process if appropriate) What would others add/delete from the above list? A repeatable process for troubleshooting for all performance issues might be difficult to gather but it were possible it would certainly be useful starting point :)

is there a recommended way to update many postgres schemes Posted: 12 Aug 2013 11:46 AM PDT I have a database in postgres which has 15 schemes, the schemes are identical with the same tables, views and functions. the system uses one scheme at a time, as if they were separate databases. So when i install an update i need to update the 15 schemes in the database. My problem comes when we update the database with changes in functions and triggers, because I need to modify in each function what scheme uses for each scheme; example: CREATE OR REPLACE FUNCTION cliente11."restablecerComprobante"("numeroComprobante" text) RETURNS integer AS $BODY$ DECLARE idComprobante INTEGER; DECLARE tipoComprobante TEXT; DECLARE idCliente INTEGER; DECLARE fechaComprobante DATE; DECLARE totalComprobante NUMERIC; DECLARE estadoComprobante INTEGER; DECLARE percepcionIIBB NUMERIC; DECLARE percepcionIIBBxKg NUMERIC; DECLARE percepcionIVA NUMERIC; BEGIN -- cargo los datos del comprobante SELECT id, tipo, cli_id, fecha_comprobante, total_comprobante, percepcion_iibb, percepcion_iibb_kg, percepcion_iva INTO idComprobante, tipoComprobante, idCliente, fechaComprobante, totalComprobante, percepcionIIBB, percepcionIIBBxKg, percepcionIVA FROM cliente11.lista_comprobantes_a_liberar WHERE estado = 0 AND numero_comprobante ILIKE "numeroComprobante"; -- otras consultas... -- genero las comisiones SELECT cliente11."generarComisionesVenta"(idComprobante, tipoComprobante) INTO estadoComprobante; RETURN 1; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; Is there a more practical way to update the 15 schemes without having to modify the script to run in each scheme?

ORA-01031 while creating a view as DBA Posted: 12 Aug 2013 12:25 PM PDT I'm trying to create a view under my DBA schema on Oracle XE. I already did that on the productive database and it worked with a user with a lot lesser privileges than the DBA role. But now I always get ORA-01031 errors. Here is my query: CREATE OR REPLACE VIEW usr_v_user_not_reg AS SELECT username "User", db_instance "Instance", ( CASE WHEN username IN ( SELECT username FROM sys.dba_users MINUS SELECT username FROM usr_t_user_reg ) THEN 'not registered' WHEN username IN ( SELECT username FROM usr_t_user_reg MINUS SELECT username FROM sys.dba_users ) THEN 'no longer present' END ) "Status" FROM usr_t_user_reg WHERE db_instanz = 'TEST_DB' AND username NOT IN ( SELECT username FROM usr_t_user_reg INTERSECT SELECT username FROM sys.dba_users ) ; And this is the output I get: Line: 10 Column:20 SQL-Error: ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges" *Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login. I even tried it with the XE SYS account, but with the same result. Am I missing something important?

Percona mysql server slow queries Posted: 12 Aug 2013 09:04 PM PDT Recently I bought new database server and installed percona mysql server 5.5.32-31 and transferred my /var/lib/mysql directory from old database server having Percona mysql server 5.5.28 to new server. My both servers configurations files are same. The new server has double storage and thrice Ram than old one. I am getting slow queries on new database server.

Possible to run multiple Reporting Services (SSRS) instances on different machines with databases on same machine? Posted: 12 Aug 2013 11:18 AM PDT With SQL Server 2012, can SSRS instances be setup on two different machines, with both instances using the same machine for their SQL Server databases? Machine 1 SQL Server 2012 Database Server SSRS instance 1 Machine 2 SSRS instance 2 pointed to machine 1's database server There's this document describing installing multiple instances in 2005, but on the same machine. Can you point me to a better resource for our design? Is this still supported on 2012? How are the SSRS databases (ReportServer, ReportServerTempDB) created for the 2nd instance?

Oracle won't start Posted: 12 Aug 2013 12:24 PM PDT While it was working fine, I had to stop the server once. When trying to start Oracle using a script we have, I got the following error: SQL> Connected to an idle instance. SQL> ORA-01081: cannot start already-running ORACLE - shut it down first SQL> Disconnected Also when trying to start SQL Plus manually AS SYSDBA I get: ERROR: ORA-12162: TNS:net service name is incorrectly specified Using SQL Plus with other users, I get: ERROR: ORA-12537: TNS:connection closed Any help appreciated ...

MariaDB Galera Cluster Replication error, no state received? Posted: 12 Aug 2013 11:29 AM PDT I have a very minimal, fresh out of the box setup for MariaDB with Galera clustering. My master node of the cluster is able to run, and is reporting that there is only one node attached to the cluster, the master itself. When I try attaching the second node to the cluster I am receiving a state received error and the process errors out, and fails. The configuration on the master looks like this: [mariadb] wsrep_cluster_address=gcomm:// wsrep_provider=/usr/lib64/galera/libgalera_smm.so binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 innodb_locks_unsafe_for_binlog=1 This is in /etc/my.cnf.d/zabbix_cluster.cnf. The slave node looks similar, except it has the name of the Master node in it. When I run service mysql restart on the slave node, the output says that MySQL was start successfully, but when I do pgrep mysql it returns nothing. Upon examination of the /var/log/mysql/error.log I initialized, it says that there is a state receive error, and will never receive state. The output looks like this: 130806 10:10:15 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 130806 10:10:15 mysqld_safe WSREP: Running position recovery with --log_error=/tmp/tmp.uZwsHWfH6y 130806 10:10:17 mysqld_safe WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1 130806 10:10:17 [Warning] option 'general_log': boolean value '/var/log/mysql/mysqld.log' wasn't recognized. Set to OFF. 130806 10:10:17 [Warning] option 'slow_query_log': boolean value '/var/log/mysql-slow-queries.log' wasn't recognized. Set to OFF. 130806 10:10:17 [Note] WSREP: wsrep_start_position var submitted: '00000000-0000-0000-0000-000000000000:-1' 130806 10:10:17 InnoDB: The InnoDB memory heap is disabled 130806 10:10:17 InnoDB: Mutexes and rw_locks use GCC atomic builtins 130806 10:10:17 InnoDB: Compressed tables use zlib 1.2.3 130806 10:10:17 InnoDB: Using Linux native AIO 130806 10:10:17 InnoDB: Initializing buffer pool, size = 128.0M 130806 10:10:17 InnoDB: Completed initialization of buffer pool 130806 10:10:17 InnoDB: highest supported file format is Barracuda. 130806 10:10:17 InnoDB: Waiting for the background threads to start 130806 10:10:18 Percona XtraDB (http://www.percona.com) 1.1.8-29.3 started; log sequence number 1598129 130806 10:10:18 [Note] Plugin 'FEEDBACK' is disabled. 130806 10:10:18 [Note] Event Scheduler: Loaded 0 events 130806 10:10:18 [Note] WSREP: Read nil XID from storage engines, skipping position init 130806 10:10:18 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera/libgalera_smm.so' 130806 10:10:18 [Note] WSREP: wsrep_load(): Galera 23.2.4(r147) by Codership Oy <info@codership.com> loaded succesfully. 130806 10:10:18 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1 130806 10:10:18 [Note] WSREP: Reusing existing '/var/lib/mysql//galera.cache'. 130806 10:10:18 [Note] WSREP: Passing config to GCS: base_host = 10.162.111.109; base_port = 4567; cert.log_conflicts = no; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 16; gcs.fc_master_slave = NO; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = NO; replicator.causal_read_timeout = PT30S; replicator.commit_order = 3 130806 10:10:18 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1 130806 10:10:18 [Note] WSREP: Start replication 130806 10:10:18 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1 130806 10:10:18 [Note] WSREP: protonet asio version 0 130806 10:10:18 [Note] WSREP: backend: asio 130806 10:10:18 [Note] WSREP: GMCast version 0 130806 10:10:18 [Note] WSREP: (4e646cee-feaa-11e2-0800-10aa5e70a57b, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567 130806 10:10:18 [Note] WSREP: (4e646cee-feaa-11e2-0800-10aa5e70a57b, 'tcp://0.0.0.0:4567') multicast: , ttl: 1 130806 10:10:18 [Note] WSREP: EVS version 0 130806 10:10:18 [Note] WSREP: PC version 0 130806 10:10:18 [Note] WSREP: gcomm: connecting to group 'my_wsrep_cluster', peer 'zabbixcrt02:' 130806 10:10:19 [Note] WSREP: declaring 21415a01-fea8-11e2-0800-7061deb24ae4 stable 130806 10:10:19 [Note] WSREP: Node 21415a01-fea8-11e2-0800-7061deb24ae4 state prim 130806 10:10:19 [Note] WSREP: view(view_id(PRIM,21415a01-fea8-11e2-0800-7061deb24ae4,8) memb { 21415a01-fea8-11e2-0800-7061deb24ae4, 4e646cee-feaa-11e2-0800-10aa5e70a57b, } joined { } left { } partitioned { }) 130806 10:10:19 [Note] WSREP: gcomm: connected 130806 10:10:19 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636 130806 10:10:19 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0) 130806 10:10:19 [Note] WSREP: Opened channel 'my_wsrep_cluster' 130806 10:10:19 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 2 130806 10:10:19 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID. 130806 10:10:19 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.29-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server, wsrep_23.7.3.rXXXX 130806 10:10:19 [Note] WSREP: STATE EXCHANGE: sent state msg: 4eb2dd53-feaa-11e2-0800-5d7a774f5dbf 130806 10:10:19 [Note] WSREP: STATE EXCHANGE: got state msg: 4eb2dd53-feaa-11e2-0800-5d7a774f5dbf from 0 (ceszabbixcrt02) 130806 10:10:19 [Note] WSREP: STATE EXCHANGE: got state msg: 4eb2dd53-feaa-11e2-0800-5d7a774f5dbf from 1 (ceszabbixcrt03) 130806 10:10:19 [Note] WSREP: Quorum results: version = 2, component = PRIMARY, conf_id = 7, members = 1/2 (joined/total), act_id = 0, last_appl. = -1, protocols = 0/4/2 (gcs/repl/appl), group UUID = bcb32946-fea7-11e2-0800-32db11e867f1 130806 10:10:19 [Note] WSREP: Flow-control interval: [23, 23] 130806 10:10:19 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 0) 130806 10:10:19 [Note] WSREP: State transfer required: Group state: bcb32946-fea7-11e2-0800-32db11e867f1:0 Local state: 00000000-0000-0000-0000-000000000000:-1 130806 10:10:19 [Note] WSREP: New cluster view: global state: bcb32946-fea7-11e2-0800-32db11e867f1:0, view# 8: Primary, number of nodes: 2, my index: 1, protocol version 2 130806 10:10:19 [Warning] WSREP: Gap in state sequence. Need state transfer. 130806 10:10:21 [Note] WSREP: Prepared SST request: mysqldump|10.162.111.109:3306 130806 10:10:21 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification. 130806 10:10:21 [Note] WSREP: Assign initial position for certification: 0, protocol version: 2 130806 10:10:21 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (bcb32946-fea7-11e2-0800-32db11e867f1): 1 (Operation not permitted) at galera/src/replicator_str.cpp:prepare_for_IST():442. IST will be unavailable. 130806 10:10:21 [Note] WSREP: Node 1 (zabbixcrt03) requested state transfer from '*any*'. Selected 0 (zabbixcrt02)(SYNCED) as donor. 130806 10:10:21 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 0) 130806 10:10:21 [Note] WSREP: Requesting state transfer: success, donor: 0 130806 10:10:24 [Warning] WSREP: 0 (zabbixcrt02): State transfer to 1 (zabbixcrt03) failed: -2 (No such file or directory) 130806 10:10:24 [ERROR] WSREP: gcs/src/gcs_group.c:gcs_group_handle_join_msg():719: Will never receive state. Need to abort. 130806 10:10:24 [Note] WSREP: gcomm: terminating thread 130806 10:10:24 [Note] WSREP: gcomm: joining thread 130806 10:10:24 [Note] WSREP: gcomm: closing backend 130806 10:10:25 [Note] WSREP: view(view_id(NON_PRIM,21415a01-fea8-11e2-0800-7061deb24ae4,8) memb { 4e646cee-feaa-11e2-0800-10aa5e70a57b, } joined { } left { } partitioned { 21415a01-fea8-11e2-0800-7061deb24ae4, }) 130806 10:10:25 [Note] WSREP: view((empty)) 130806 10:10:25 [Note] WSREP: gcomm: closed 130806 10:10:25 [Note] WSREP: /usr/sbin/mysqld: Terminated. 130806 10:10:25 mysqld_safe Number of processes running now: 0 130806 10:10:25 mysqld_safe WSREP: not restarting wsrep node automatically 130806 10:10:25 mysqld_safe mysqld from pid file /var/lib/mysql/zabbixcrt03.pid ended I'm not sure why this is happening, or what it means. I see it can connect, but there is something that is not transferring from the Master to the Slave. What should I look for/at or do? I also have made sure that /var/lib/mysql/ was owned by mysql:mysql and it is. the permissions on the directory are: 755

Error: "Storage Engine for the Table Doesn't Support Nullable Columns" (SequelPro) Posted: 12 Aug 2013 02:00 PM PDT I'm trying to load a very normal .csv file (that was created from Excel 2011 for Mac) into SequelPro (using MySQL) with my Mac -- and I've recently started getting this error consistently. Can anybody let me know what it is and how to fix it? An error occurred while trying to add the new table 'wblist' by CREATE TABLE `wblist` ( `FILE` VARCHAR(255), `FIRSTNAME` VARCHAR(255), `MIDDLE` VARCHAR(255), `LASTNAME` VARCHAR(255), `FULLNAME` VARCHAR(255), `GENDER` VARCHAR(255), `ADDRESS` VARCHAR(255), `CITY` VARCHAR(255), `STATE` VARCHAR(255), `ZIP` VARCHAR(255), `PHONE` BIGINT(11), `UNIT` VARCHAR(255), `JOB` VARCHAR(255), `AREA` VARCHAR(255), `TIME` VARCHAR(255), `MAILINGADDRESS` VARCHAR(255), `MAILINGCITY` VARCHAR(255), `MAILINGSTATE` VARCHAR(255), `MAILINGZIP` VARCHAR(255), `ID` BIGINT(11), `CONFIDENCE` VARCHAR(255), `BIRTHDATE` VARCHAR(255), `AGE` INT(11), `RACE` VARCHAR(255), `ETHNICITY` VARCHAR(255), `RELIGION` VARCHAR(255), `PARTY` VARCHAR(255), `REGISTRATIONDATE` VARCHAR(255), `VOTERSTATUS` VARCHAR(255), `OtherPhone` VARCHAR(255), `POSSIBLEADDRESS` VARCHAR(255), `POSSIBLEMAILADDRESS` VARCHAR(255), `RECID` VARCHAR(255)) ENGINE=CSV; MySQL said: The storage engine for the table doesn't support nullable columns This is stopping me before I'm able to import the table. Thanks for the help!

optimizing MySQL for traffic analytics system Posted: 12 Aug 2013 08:01 PM PDT background : I've developed a URL shortener system like Bitly with same features , so the system also tracks clickers info and represent as graphs to the person who has shorten the link as analytics data. currently I'm using MySQL and have a table to store click info with this schema: visit_id (int) ip (int) date (datetime) country browser device os referrer (varchar) url_id (int) //as foreign key to the shortened URL and for now , just the url_id field has index The system should represent click analytics in the time periods the user wants, for example past hour, past 24 hours , the past month , ... for example to generate graphs for past month , I do following queries: SELECT all DAY(date) AS period, COUNT( * ) FROM ( SELECT * FROM visits WHERE url_id = '$url_id' ) AS URL WHERE DATE > DATE_SUB( CURRENT_TIMESTAMP( ) , INTERVAL 1 MONTH ) GROUP BY DAY( DATE ) //another query to display clicker browsers in this period //another query to display clicker countries in this period // ... issues: for a shortened link with about 500,000 clicks , it takes about 3-4 seconds to calculate just the first query , so for total queries about 10-12 seconds which is terrible. lots of memory and CPU is needed to run such queries questions : 1- how to improve and optimize the structure , so the analytics of high traffic links will be shown in less than 1 second(like bitly and similar web apps) and with less usage of CPU and RAM ? should I make an index on the fields date, country, browser, device, os, referrer ? if yes , how to do that for the field date because I should group clicks some times by DAY(date), sometimes by HOUR(date), sometimes by MINUTE(date) and ... 2- is MySQL suitable for this application? assume at maximum my application should handle 100 million links and 10 billion clicks on them totally. Should I consider switching to an NoSQL solution for example? 3- if MySQL is ok , is my database design and table structure proper and well designed for my application needs? or you have better recommendations and suggestions? UPDATE: I made an index on column referrer but it didn't help at all and also damaged the performance and I think that's because of the low cardinality of this column (also others) and the big resulting index size related to the RAM of my server. I think making index on these columns would not help to solve my problem, my idea is about one of these: 1- if using MySQL, maybe generating statistics using background processing for high traffic links is better instead of calculating lively at the user request. 2- using some caching solution like memcached to help MySQL with high traffic links. 3- using a NoSQL such as MongoDB and solutions like Map-Reduce which I am poorly familiar with and haven't used ever. what do you think?

LDAP in SQL Server 2012 Posted: 12 Aug 2013 06:01 PM PDT In our current system, we query the LDAP using SQL Server 2008R2 but are moving to SQL Server 2012 where we have hit a snag. According to http://msdn.microsoft.com/en-us/library/cc707782.aspx one of the discontinued features of SQL Server 2012 is the AD helper service. Does anyone know of a way to query the AD from SQL Server 2012?

Primary replica set server goes secondary after secondary fails Posted: 12 Aug 2013 05:01 PM PDT I have a 2 servers replica set that, after the secondary fails the primary goes into secondary mode while the secondary is in STARTUP2 (recovering). The problem with this is that I can't use the collection stored in that replica set freely, I'm getting errors trying to use the collection: pymongo.errors.OperationFailure: database error: ReplicaSetMonitor no master found for set: rs2 Sometimes if I restart the mongod instances, the server rs2-1 is the primary for a while, but after some time (while the secondary is recovering) I see this in the logs of rs2-1 (the primary): Tue May 7 17:43:40.677 [rsHealthPoll] replSet member XXX.XXX.XXX.XXX:27017 is now in state DOWN Tue May 7 17:43:40.677 [rsMgr] can't see a majority of the set, relinquishing primary Tue May 7 17:43:40.682 [rsMgr] replSet relinquishing primary state Tue May 7 17:43:40.682 [rsMgr] replSet SECONDARY Tue May 7 17:43:40.682 [rsMgr] replSet closing client sockets after relinquishing primary Is there an easy way to make the primary keep being primary after the secondary fails? Am I doing something wrong? Thanks in advance!

MySQL backup InnoDB Posted: 12 Aug 2013 01:00 PM PDT I have a VoIP server running 24x7. At low peak hour at lease 150+ users are connected. My server has MySQL running with InnoDB engine on Windows 2008 platform. I like to take at least 2 times full database backup without shutting down my service. As per Peter Zaitsev - the founder of percona, mysqldump –single-transaction is not always good. read here if you are interested As I'm not a DBA, I like to know in my scenario, which would be best solution to take a database backup? Thanks,

Strange characters in mysqlbinlog output Posted: 12 Aug 2013 11:00 AM PDT Has anyone experienced this? Data replicates fine but when output in mysqlbinlog there are hidden characters that break the input? mysqlbinlog Ver 3.3 for Linux at x86_64 mysql 5.5.28 server Thanks! Julie

Connecting to a SQL Server database from a Flash program Posted: 12 Aug 2013 12:00 PM PDT I currently have the ability to utilize Microsoft SQL Server 2012. I am developing a project with Adobe Flash Builder 4.7. If I link my database with Adobe Flash Builder is there any additional steps I must take in order to make the database live, or as long as my computer is running will this database be accessible from any device that is utilizing it? In other words is this a LAN only system or does it automatically make itself available for the programs I link to it?

Oracle Express edition on Ubuntu - control file missing Posted: 12 Aug 2013 07:01 PM PDT I have installed the Oracle Express edition on Ubuntu as mentioned here. I am facing issues when I try to create a sample table. Started oracle $ sudo service oracle-xe start Starting Oracle Database 11g Express Edition instance. Started sqlplus $ sqlplus / as sysdba Executed the CREATE command SQL> CREATE TABLE SAMPLE (ID NUMBER); CREATE TABLE SAMPLE (ID NUMBER) *** ERROR at line 1: ORA-01109: database not open** After a series of research on web, I tried to shutdown and restart oracle: Shutdown command SQL> shutdown ORA-01507: database not mounted ORACLE instance shut down. Started the oracle instance SQL> startup ORACLE instance started. Total System Global Area 688959488 bytes Fixed Size 2229688 bytes Variable Size 411044424 bytes Database Buffers 272629760 bytes Redo Buffers 3055616 bytes ORA-00205: error in identifying control file, check alert log for more info I realized that the control file is missing at /u01/app/oracle/oradata/XE. XE is the DB name. So I tried to create the control file as follows: SQL> CREATE CONTROlFILE SET DATABASE XE RESETLOGS; Control file created. Tried to create the sample table again SQL> CREATE TABLE SAMPLE(ID NUMBER); CREATE TABLE SAMPLE(ID NUMBER) ERROR at line 1: ORA-01109: database not open So I tried to issue the following command SQL> ALTER DATABASE OPEN RESETLOGS; ALTER DATABASE OPEN RESETLOGS ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent** ORA-01110: data file 1: '/u01/app/oracle/product/11.2.0/xe/dbs/dbs1XE.dbf' What should be done next? I am clueless as I am not a database guy. Note: Output of $ lsnrctl services LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 04-JAN-2013 09:15:37 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Service "XE" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER The command completed successfully

Is using multiple foreign keys separated by commas wrong, and if so, why? Posted: 12 Aug 2013 04:11 PM PDT There are two tables: Deal and DealCategories. One deal can have many deal categories. So the proper way should be to make a table called DealCategories with the following structure: DealCategoryId (PK) DealId (FK) DealCategoryId (FK) However, our outsource team stored the multiple categories in the Deal table this way: DealId (PK) DealCategory -- In here they store multiple deal ids separated by commas like this: 18,25,32. I feel that what they did is wrong, but I don't know how to clearly explain why this is not right. How should I explain to them that this is wrong? Or maybe I'm the one who's wrong and this is acceptable?

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