| MySQL Query: Delete from 'table2' where column = 'value' IF column in 'table1' = 'value' Posted: 10 Jul 2013 08:59 PM PDT I am trying to execute a MySQL query to delete rows from 'table2' where column = 'value' IF column in 'table1' = 'value' I have 2 tables... Table 1 is called 'accounts' Table 2 is called 'inventoryitems' The column in question for 'accounts' is called 'banned' The column in question for 'inventoryitems' is called 'itemid' I would like to DELETE FROM inventoryitems WHERE itemid = 2340000 IF... the column banned in accounts has a value of 1 You can join the table accounts to inventoryitems by a 3rd table called characters. Table accounts has columns: id (primary key) and banned. Table characters has columns: characterid and accountid (accountid links to id in table accounts). Table inventoryitems has columns itemid and characterid (characterid links to characterid in table characters) Hope I have provided enough information... Many thanks! |
| Why does Postgres generate an already used PK? Posted: 10 Jul 2013 08:46 PM PDT I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists.
My database does in fact have a myapp_mymodel object with the primary key of 1. Why would Postgres attempt to use that primary key again? Or, is this most likely my application (or Django's ORM) causing this? Note: I'm using Postgres. |
| Push Data from MySQL to MSSQL Posted: 10 Jul 2013 08:10 PM PDT I am not sure if my title is correct, but I will try my best to explain the current problem. Current Scenario: - I have a php web application with MySQL Database(lets call it DB1) which is stored in Machine A.
- I have another php web application with MySQL Database(lets call it DB2) whch is stored in Machine B,
- ASP.Net Application with MSSQL Database(lets call it DB3) which is stored in Machine C which will serve as a ledger.
- They are connected via VPN.
I have completed this: - Lets say the transaction will go like this, a new transaction occurs, it will validate records on both code behind A and push to through Web Service on Machine B for validation and posting(Transaction ID, and corresponding Transaction Details)
- I will then get the response from B and if successful I will proceed to the problem below.
I have been reading and trying the following: - MySQL connector ODBC: It is working coz I use it to Linked Server MySQL in MSSQL.
- Linked Server on MSSQL: I am not sure though if this is the solution for my current problem, but at the moment I can view and query DB1 from DB3, but the trigger should be in DB1 to push the record to DB3 everytime there is a new record inserted on DB1.
- Transactional replication: Not sure if it will work out
- SSIS: I have tried to add both DB1 and DB3 by creating Integration Service and used mysql connector odbc and added dsn(both user and system) but there is an error like what I have mentioned below
- MySQL Data Controller: Been reading, have not tried due coz I need to recompile feetds lib with distributive pack of MySQL
- MySQL Federated Table: But this only works for MySQL(Both end)
- Incremental load:
Problems: - How can I 'push' the record from DB1(MySQL) to DB3(MSSQL) and if successful get a valid response(ID preferably so that I can have a reference on both DB1 and DB3(via trigger in DB1)
- I have tried SSIS, successfully added ADO.Net Souce for 127.0.0.1 for DB3(the SSIS resides on Machine B), but when I am adding a connection to the DB1 that resides on Machine B I get an error("Error[IM002][Microsoft][ODBC Manager] Data Source Not found and no default driver found")
- I have tried Linked server but somehow it only shows DB1 from DB3, any software or addons/plugins so I can view DB3 from DB1 ad push the records from DB1?
- I need to have it as a transactional replication(somehow I read it is possible but at the moment I can only search for MSSQL to MSSQL)
- I cannot put the inserting of new records/updating records on Code Behind(which is possible and easier) for security reasons(just incase there is/are changes on either DB). Same goes for the usage on Web Service for this part.
- Performance likewise, will it be wise to do this if the number of transaction is large?(probably 360,000 inserted records in a daily basis)
Any detail explanation is greatly appreciated. Thanks Taken from my question in: http://stackoverflow.com/questions/17583924/push-data-from-mysql-to-mssql |
| Stored Procedure against Linked Server Posted: 10 Jul 2013 03:29 PM PDT We have an AX server that we would like a group of users to be able to retrieve data from but limit their access to just the data needed. Normally this could be done with a stored procedure and give the the group permission to only execute the stored procedure and use the "execute as" to have the procedure run under an account that does have access to the underlying tables. However AX controls the lifetime of stored procedure so we can't just create an arbitrary stored procedure and call it good. To remedy the situation, we would instead have the stored procedure on another SQL Server and use a Linked Server so that the users could get the data they need but not have direct access to the tables of the AX server. The problem is in setting this up we keep getting: Msg 7437, Level 16, State 1, Line 3 Linked servers cannot be used under impersonation without a mapping for the impersonated login. So the question is: How do we get this scenario to work with Linked Servers or is there a better way to have users get the data they need without direct access to the AX server? |
| Geographic Hierarchies in a OLAP Cube / Data Warehouse Posted: 10 Jul 2013 01:51 PM PDT Are the following geographic hierarchies correct? Do any US/Canadian area codes cross state/province lines? Do any US/Canadian counties cross state/province lines? Are there countries with counties but no principal country divisions (states/provinces)? Zip / Postal Codes US Style Continent > Country > State/Province > Postal/Zip UK Style Continent > Country > Postal Code Towns / Cities US Style Continent > Country > State/Province > City UK Style Continent > Country > City Area Codes US Style Continent > Country > State/Province > Area Code UK Style Continent > Country > Area Code Counties US Style Continent > Country > State/Province > County UK Style Continent > Country > County |
| What could I possibly be doing wrong with this update statement Posted: 10 Jul 2013 01:45 PM PDT UPDATE pcdbak SET pcdbak.Title = pcd.Title FROM pcd, pcdbak WHERE pcdbak.eCommNum = pcd.eCommNum
This was the error message: [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM pcd, pcdbak WHERE pcdbak.eCommNum = pcd.eCommNum' at line 4 |
| MAMP PRO not showing all MAMP database Posted: 10 Jul 2013 04:14 PM PDT I was earlier using MAMP and Now switched to MAMP PRO. I used to use MAMP mysql as root default user and having lots off database as that user. If i start MAMP and go to terminal with mysql command and do show databases; then it show me all databases. But if i start MAMP PRO and goto terminal and do show databases; then it show me only 4 databases mysql, information_schema, performance_schema and one db which is common for both MAMP and MAMP PRO. How can i use those existing DB on MAMP under MAMP PRO. |
| Connection Actively Refused? Posted: 10 Jul 2013 11:50 AM PDT This is probably a pretty generic question, but I have a small test app thats trying to connect to a Database. The Application is a simple socket application and is simply writing to a table in a database. So I probably need to add the Login to the security settings in the database. The app is running from an Administrator account called "User".....thats basically all the details I know, the App is nothing special but The Database is on a completely different server. Would I just need to add the Login and then map the user mappings to the Database I want to use? and what would the Login be? NT AUTHORITY\User or something similar? Thanks |
| What is the most effective way to compress and store a MS SQL Backup? Posted: 10 Jul 2013 03:39 PM PDT I've been doing some testing of different methods for compressing and storing MS SQL Backups (using MS SQL 2008 R2 Enterprise edition), and I'm wondering what the most effective compression algorithm is for long term storage of those backups, outside of SQL's internal compression algorithms. I'm not worried about the physical storage or tape drives or anything, just trying to turn our 3TB of data and log files into the smallest single file I can. So, for example, would a .zip or .7z? Or are there too many variables within my database to be able to accurately estimate what will be the most effective and I'll just need to do some tests? Or is SQL's internal compression the best I'll get? |
| Changing the mysql.proc table directly after upgrade Posted: 10 Jul 2013 11:56 AM PDT Our host upgraded our mysql install without us knowing from roughly 5.1 to 5.5. This caused a problem because the table definition of mysql.proc is different as discussed here: http://bugs.mysql.com/bug.php?id=50183 Basically, our stored procedure won't run because one column changed (comment) between 5.1 and 5.5 My question is, can I safely run this command to fix the problem: alter table mysql.proc modify comment text;
I don't want to run mysql_upgrade as that will lock every table in the database on our production server. |
| How to create a mysqldump file automatically from SQL server database on a daily basis Posted: 10 Jul 2013 02:21 PM PDT I want to create a mysqldump file from a SQL Server database, I know its possible using MYSQL workBench, what i want is to make it completely automate. So the script runs on a daily or weekly basis and create a copy of MSSQL database and update mysql database with the copy. Is there any tool or script which can do that task. |
| Changing password doesn't work Posted: 10 Jul 2013 11:00 AM PDT I need to login to a database. I have good credentials, but when I was trying to login with SQL Developer, I got this error message: ORA-28001: the password has expired Then I tried to login with SQL*Plus, which worked fine, I connected successfully and I was asked to change my password. So I typed in a new password and then retyped it. Then I got other message: ORA-01017: invalid username/password; logon failed Can you help me how can I change this password? I don't have administrative permissions. |
| Query Plans not retained insufficient memory errors Posted: 10 Jul 2013 12:36 PM PDT We have been experiencing memory issues with SQL Server. We first realised we had a problem when we started getting timeouts and login errors: A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) Looking into event viewer on our sqlbox, we noticed a multitude of insufficient memory errors: There is insufficient system memory to run this query.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. The only immediate warning prior to this was the following message: AppDomain 119 (Alerts.dbo[runtime].118) unloaded. About twenty minutes prior to this, we had a number of perf related messages and errors: info:
The Microsoft Operations Manager Agent on this computer received new rules and configuration settings from its MOM Server. Management Group: GGC warning:
The configuration information of the performance library "C:\WINDOWS\system32\aspperf.dll" for the "ASP" service does not match the trusted performance library information stored in the registry. The functions in this library will not be treated as trusted. error:
The Microsoft Operations Manager performance provider could not access performance counters on computer blah-blah-blah. Microsoft Operations Manager will not monitor performance counters on this computer until they become available. info:
The Microsoft Operations Manager successfully loaded performance counters on computer blah-blah-blah after previous failure(s) and will start monitoring them. I doubt the above perf alerts/errors had anything to do with the two hours of "insufficient memory exceptions, but I have included the messages just in case. Finally, after two hours of red memory errors, the following info message heralded the end of the insufficient memory alerts: SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations. So freeprocache was called by our DBA at some point. Despite eventually fixing the insufficient memory exceptions, we noticed that our execution plans were still not being stored. This 'issue' has now continued for 3 whole days, meaning that apps using queries with complex plans are facing sever performance difficulties. There are points where the plans start to get taken again, but they don't ever tend to stay in the cache for long. I'm wondering if anyone could help with pinpointing the area of concern. Part A represents the system when the query plans are being kept (plans being retained, but only for an hour or so), and Part B represents when the plans are not being cached at all (checking dm_exec_query_stats) PART A DBCC MemoryStatus results: Memory Manager KB VM Reserved 1828768 VM Committed 269928 AWE Allocated 13762560 Reserved Memory 1024 Reserved Memory In Use 0 Memory node Id = 0 KB VM Reserved 1824608 VM Committed 265920 AWE Allocated 13762560 MultiPage Allocator 50776 SinglePage Allocator 656568 MEMORYCLERK_SQLGENERAL (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 14672 MultiPage Allocator 11144 MEMORYCLERK_SQLBUFFERPOOL (Total) KB VM Reserved 1620024 VM Committed 137272 AWE Allocated 13762560 SM Reserved 0 SM Commited 0 SinglePage Allocator 0 MultiPage Allocator 3624 MEMORYCLERK_SQLQUERYEXEC (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 80 MultiPage Allocator 32 MEMORYCLERK_SQLOPTIMIZER (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 5568 MultiPage Allocator 88 MEMORYCLERK_SQLUTILITIES (Total) KB VM Reserved 360 VM Committed 360 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 664 MultiPage Allocator 0 MEMORYCLERK_SQLSTORENG (Total) KB VM Reserved 17792 VM Committed 17792 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 11200 MultiPage Allocator 13040 MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 4504 MultiPage Allocator 0 MEMORYCLERK_SQLCLR (Total) KB VM Reserved 126336 VM Committed 54816 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1296 MultiPage Allocator 2872 MEMORYCLERK_SQLSERVICEBROKER (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 176 MultiPage Allocator 192 MEMORYCLERK_SQLHTTP (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 MEMORYCLERK_SNI (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 432 MultiPage Allocator 16 MEMORYCLERK_FULLTEXT (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 MEMORYCLERK_SQLXP (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 MEMORYCLERK_BHF (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 720 MultiPage Allocator 0 MEMORYCLERK_SQLQERESERVATIONS (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 37896 MultiPage Allocator 0 MEMORYCLERK_HOST (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 224 MultiPage Allocator 96 MEMORYCLERK_SOSNODE (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16008 MultiPage Allocator 9136 CACHESTORE_OBJCP (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 297080 MultiPage Allocator 4448 CACHESTORE_PHDR (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16400 MultiPage Allocator 0 CACHESTORE_XPROC (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 112 MultiPage Allocator 0 CACHESTORE_TEMPTABLES (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 48 MultiPage Allocator 0 CACHESTORE_NOTIF (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 CACHESTORE_VIEWDEFINITIONS (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 CACHESTORE_XMLDBTYPE (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_XMLDBELEMENT (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_XMLDBATTRIBUTE (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_STACKFRAMES (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 0 MultiPage Allocator 8 CACHESTORE_BROKERTBLACS (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 296 MultiPage Allocator 0 CACHESTORE_BROKERKEK (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_BROKERDSH (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_BROKERRSB (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_BROKERREADONLY (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 32 MultiPage Allocator 0 CACHESTORE_BROKERTO (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_EVENTS (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 CACHESTORE_SYSTEMROWSET (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 3104 MultiPage Allocator 0 USERSTORE_SCHEMAMGR (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 9592 MultiPage Allocator 144 USERSTORE_DBMETADATA (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 6800 MultiPage Allocator 0 USERSTORE_TOKENPERM (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 752 MultiPage Allocator 0 USERSTORE_OBJPERM (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 6072 MultiPage Allocator 0 USERSTORE_SXC (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 2232 MultiPage Allocator 0 OBJECTSTORE_LBSS (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 472 MultiPage Allocator 0 OBJECTSTORE_SNI_PACKET (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 7640 MultiPage Allocator 48 OBJECTSTORE_SERVICE_BROKER (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 256 MultiPage Allocator 0 OBJECTSTORE_LOCK_MANAGER (Total) KB VM Reserved 4096 VM Committed 4096 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 2584 MultiPage Allocator 0 Buffer Distribution Buffers Stolen 11432 Free 283 Cached 75066 Database (clean) 1609444 Database (dirty) 24091 I/O 0 Latched 4 Buffer Counts Buffers Committed 1720320 Target 1720320 Hashed 1633540 Stolen Potential 89382 External Reservation 308 Min Free 712 Visible 185344 Available Paging File 2268071 Procedure Cache Value TotalProcs 6240 TotalPages 71392 InUsePages 407 Global Memory Objects Buffers Resource 1127 Locks 326 XDES 204 SETLS 8 SE Dataset Allocators 16 SubpDesc Allocators 8 SE SchemaManager 1194 SQLCache 749 Replication 126 ServerGlobal 27 XP Global 2 SortTables 1523 Query Memory Objects Value Grants 2 Waiting 0 Available (Buffers) 78932 Maximum (Buffers) 83833 Limit 83833 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Last Target 88245 Small Query Memory Objects Value Grants 0 Waiting 0 Available (Buffers) 4410 Maximum (Buffers) 4410 Limit 4410 Optimization Queue Value Overall Memory 1216954368 Target Memory 556490752 Last Notification 1 Timeout 6 Early Termination Factor 5 Small Gateway Value Configured Units 32 Available Units 32 Acquires 0 Waiters 0 Threshold Factor 250000 Threshold 250000 Medium Gateway Value Configured Units 8 Available Units 8 Acquires 0 Waiters 0 Threshold Factor 12 Big Gateway Value Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 MEMORYBROKER_FOR_CACHE Value Allocations 75056 Rate 15 Target Allocations 136125 Future Allocations 0 Last Notification 1 MEMORYBROKER_FOR_STEAL Value Allocations 6869 Rate 8 Target Allocations 67931 Future Allocations 0 Last Notification 1 MEMORYBROKER_FOR_RESERVE Value Allocations 4737 Rate 1844 Target Allocations 94128 Future Allocations 33074 Last Notification 1
The available memory and largest free contiguous block: Total avail Mem, KB Max free size, KB 17828 4148
PART B: DBCC MemoryStatus Memory Manager KB VM Reserved 1823056 VM Committed 264192 AWE Allocated 13762560 Reserved Memory 1024 Reserved Memory In Use 0 Memory node Id = 0 KB VM Reserved 1818896 VM Committed 260184 AWE Allocated 13762560 MultiPage Allocator 41672 SinglePage Allocator 90880 MEMORYCLERK_SQLGENERAL (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 9456 MultiPage Allocator 12416 MEMORYCLERK_SQLBUFFERPOOL (Total) KB VM Reserved 1620024 VM Committed 137272 AWE Allocated 13762560 SM Reserved 0 SM Commited 0 SinglePage Allocator 0 MultiPage Allocator 3624 MEMORYCLERK_SQLQUERYEXEC (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 88 MultiPage Allocator 0 MEMORYCLERK_SQLOPTIMIZER (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 11344 MultiPage Allocator 88 MEMORYCLERK_SQLUTILITIES (Total) KB VM Reserved 600 VM Committed 600 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 672 MultiPage Allocator 0 MEMORYCLERK_SQLSTORENG (Total) KB VM Reserved 17856 VM Committed 17856 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 11408 MultiPage Allocator 13040 MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 2648 MultiPage Allocator 0 MEMORYCLERK_SQLCLR (Total) KB VM Reserved 126336 VM Committed 54816 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1296 MultiPage Allocator 2872 MEMORYCLERK_SQLSERVICEBROKER (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 184 MultiPage Allocator 192 MEMORYCLERK_SQLHTTP (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 MEMORYCLERK_SNI (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 488 MultiPage Allocator 16 MEMORYCLERK_FULLTEXT (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 MEMORYCLERK_SQLXP (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 MEMORYCLERK_QSRANGEPREFETCH (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 392 MultiPage Allocator 0 MEMORYCLERK_BHF (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 456 MultiPage Allocator 0 MEMORYCLERK_SQLQERESERVATIONS (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 21512 MultiPage Allocator 0 MEMORYCLERK_HOST (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 224 MultiPage Allocator 96 MEMORYCLERK_SOSNODE (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 14904 MultiPage Allocator 8992 CACHESTORE_OBJCP (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 3816 MultiPage Allocator 0 CACHESTORE_SQLCP (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 328 MultiPage Allocator 0 CACHESTORE_PHDR (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1760 MultiPage Allocator 0 CACHESTORE_XPROC (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 CACHESTORE_TEMPTABLES (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 CACHESTORE_NOTIF (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 CACHESTORE_VIEWDEFINITIONS (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 CACHESTORE_XMLDBTYPE (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_XMLDBELEMENT (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_XMLDBATTRIBUTE (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_STACKFRAMES (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 0 MultiPage Allocator 8 CACHESTORE_BROKERTBLACS (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 304 MultiPage Allocator 0 CACHESTORE_BROKERKEK (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_BROKERDSH (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_BROKERRSB (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_BROKERREADONLY (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 32 MultiPage Allocator 0 CACHESTORE_BROKERTO (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_EVENTS (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 CACHESTORE_SYSTEMROWSET (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 744 MultiPage Allocator 0 USERSTORE_SCHEMAMGR (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 9560 MultiPage Allocator 144 USERSTORE_DBMETADATA (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 2960 MultiPage Allocator 0 USERSTORE_TOKENPERM (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1064 MultiPage Allocator 0 USERSTORE_OBJPERM (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 6144 MultiPage Allocator 0 USERSTORE_SXC (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 88 MultiPage Allocator 0 OBJECTSTORE_LBSS (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 160 MultiPage Allocator 0 OBJECTSTORE_SNI_PACKET (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 7464 MultiPage Allocator 48 OBJECTSTORE_SERVICE_BROKER (Total) KB VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 256 MultiPage Allocator 0 OBJECTSTORE_LOCK_MANAGER (Total) KB VM Reserved 4096 VM Committed 4096 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1680 MultiPage Allocator 0 Buffer Distribution Buffers Stolen 8130 Free 3291 Cached 4433 Database (clean) 1685517 Database (dirty) 18929 I/O 4 Latched 16 Buffer Counts Buffers Committed 1720320 Target 1720320 Hashed 1704528 Stolen Potential 162293 External Reservation 1538 Min Free 1024 Visible 185344 Available Paging File 2256907 Procedure Cache Value TotalProcs 11 TotalPages 813 InUsePages 137 Global Memory Objects Buffers Resource 1141 Locks 213 XDES 225 SETLS 8 SE Dataset Allocators 16 SubpDesc Allocators 8 SE SchemaManager 1190 SQLCache 80 Replication 126 ServerGlobal 27 XP Global 2 SortTables 1523 Query Memory Objects Value Grants 1 Waiting 0 Available (Buffers) 129608 Maximum (Buffers) 132297 Limit 132297 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Last Target 139260 Small Query Memory Objects Value Grants 0 Waiting 0 Available (Buffers) 6963 Maximum (Buffers) 6963 Limit 6963 Optimization Queue Value Overall Memory 1216954368 Target Memory 1142743040 Last Notification 1 Timeout 6 Early Termination Factor 5 Small Gateway Value Configured Units 32 Available Units 28 Acquires 4 Waiters 0 Threshold Factor 250000 Threshold 250000 Medium Gateway Value Configured Units 8 Available Units 8 Acquires 0 Waiters 0 Threshold Factor 12 Threshold 23807146 Big Gateway Value Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 MEMORYBROKER_FOR_CACHE Value Allocations 4347 Rate -840 Target Allocations 136775 Future Allocations 0 Last Notification 1 MEMORYBROKER_FOR_STEAL Value Allocations 6929 Rate -702 Target Allocations 139495 Future Allocations 0 Last Notification 1 MEMORYBROKER_FOR_RESERVE Value Allocations 2689 Rate -60 Target Allocations 148554 Future Allocations 33074 Last Notification 1
Memory left: Total avail Mem, KB Max free size, KB 18228 2580
PART A and PART B are both taken at points where memory is low. The difference is that with B, it seems that the query plans are not being retained for any period of time at all (where in A they are being held for an hour or so). I'm hoping that someone can look at the memory statuses and possibly point me in the direction of where the problem resides. Also, we are on SQL Server 2005 Server Pack 3. UPDATE OK, I was looking at the MemoryStatus above, and noticed that the object cache store was at 297MB in PART A. Is this high for running on 32 bit? Will this not consume the majority of VAS? I wanted to take a look at this in a bit more detail: So, I've been running this query: --Get the size of the Plan Cache (CACHESTORE_SQLCP is non-SP and CACHESTORE_OBJCP is SP) SELECT (SUM(single_pages_kb) + SUM(multi_pages_kb) ) / (1024.0 * 1024.0) AS [Plan Cache Size(GB)] FROM sys.dm_os_memory_cache_counters WHERE type = 'CACHESTORE_SQLCP' OR type = 'CACHESTORE_OBJCP'
It seems that this is cycling every two minutes or so, with the Stores being flushed every tim it stats to rise above 200MB. The majority of this (as in 180MB) is in CACHESTORE_OBJCP. Would I be right in thinking I can use the following query to then analyse the object cache? select TOP 8000 objtype, usecounts, p.size_in_bytes/1024 'IN KB', LEFT([sql].[text], 100) as [text] from sys.dm_exec_cached_plans p outer apply sys.dm_exec_sql_text (p.plan_handle) sql ORDER BY 'IN KB' DESC
The above query, taken at around about the highest point in CACHE MB, returns around 1500 objects. More or Less, the top 25 in terms of size seem to be triggers, with the sum size 65MB. Is this normal? Am I barking up the wrong tree? |
| Improve CTE performance Posted: 10 Jul 2013 06:34 PM PDT is there a way to improve the performance of a recursive CTE like below, I am unsure if I can add indexes on joins when the joins are using ROW_NUMBER? DECLARE @File_Name VARCHAR(8000), @Disk VARCHAR(5) SET @File_Name = 'MARSQLUTILITY,AdventureWorksDW_Data' SET @Disk = 'I:' --Code to pull out deltas between collected IO stats. ;WITH IOPS ([IO_STALL] ,[IO_STALL_READ_MS] ,[IO_STALL_WRITE_MS] ,[NUM_OF_READS] ,[NUM_OF_WRITES] ,[SIZE_ON_DISK_MB] ,[DBNAME] ,[NAME] ,[FILE_ID] ,[DB_FILE_TYPE] ,[DISK] ,[FILE_LOCATION] ,[TIMESTAMP] ,[ROW]) AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY FILE_LOCATION ORDER BY TIMESTAMP DESC) AS [ROW] FROM dbo.DISKIOPS ) --Need to divide by the number of operations in that timeframe to get average wait time per operation. --SELECT MAX(([IO2].[IO_STALL] - [IO1].[IO_STALL]) / (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES)) SELECT [IO1].[TIMESTAMP], [IO1].[NAME], ([IO2].[IO_STALL] - [IO1].[IO_STALL]) / (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES) AS Avg_Stall_Per_Operation FROM IOPS IO1 JOIN IOPS IO2 ON IO1.ROW = (IO2.ROW+1) WHERE IO1.NAME = IO2.NAME --Need to make sure not dividing by 0 when there has been no operations AND (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES) > 0 AND IO1.Disk = @Disk
|
| Getting related data from an uncertain column Posted: 10 Jul 2013 11:32 AM PDT I am working on a friendship logic, the table friendship has this structure: `friendship_id` INT NOT NULL , `user1_id` INT NOT NULL , `user2_id` INT NOT NULL , `status` INT NULL , `created` DATETIME NULL , `message` VARCHAR(255) NULL ,
And the profile table. `profile_id` INT NOT NULL , `user_id` INT NOT NULL , `first_name` VARCHAR(255) NULL , `last_name` VARCHAR(255) NULL , `image_url` VARCHAR(255) NULL , `gender` INT NULL , `city_id` INT NULL , `country_id` INT NULL ,
I am trying to get a list of the the friends of a certain user, but I want related info also, for example their city. The problem is the user_id could be either in user1_id or user2_id. I can try with a left join but the results are not ok: select city.name, p1.first_name as 'user1_first_name', p1.last_name as 'user1_last_name', p1.user_id as 'user1_id', p1.profile_id as 'user1_profile_id', p1.image_url as 'user1_image', p2.first_name as 'user2_first_name', p2.last_name as 'user2_last_name', p2.user_id as 'user2_id', p2.profile_id as 'user2_profile_id', p2.image_url as 'user2_image' from friendship f left join profile p1 on p1.user_id = user1_id left join profile p2 on p2.user_id = user2_id left join city on p1.user_id = 1 and city.city_id = p1.city_id or city.city_id = p2.city_id where f.user1_id = 1 or f.user2_id = 1 and f.status = 2
Note: Probably my sql is not the best thing you'll ever see :) What's the best way to get the information? |
| SQL Server Trace - Filter on System Processes Posted: 10 Jul 2013 08:26 PM PDT Is there a way to filter a (2008 R2) profiler trace on a specific system process? I don't mean the usual server process (SPID), but the system processes that append "s" onto the end of a SPID (eg .spid29s) as seen when looking at the SQL Server Logs. The system processes are seen a lot when a server is first started. |
| What is difference between tempdb log and database log? In which circumstances these log files uses? Posted: 10 Jul 2013 06:01 PM PDT As we all know there are two types of log file (*.ldf) comes into the picture whenever any large query getting executed 1. database log (transaction log) file 2. tempdb log. Please describe me how SQL server uses thes files while any query executed? 2nd thing i want to know, when it is safe to shrink log files (database log, tempdb log)? In which case we shouldn't log files? |
| Database restructure - beneficial? Posted: 10 Jul 2013 12:37 PM PDT I have a table for email messages. Then, I have a table that has the message parts. The Parts table contains a field_id, message_id, and data
At the time I used Parts to name the table and yet used field_id for the column. Just an FYI So for example, a part_id of 2 would be the subject of the message. I have parts for subject, date, htmlbody, and textbody. Due to this structure, I have approximately 2 more queries per email (one for the parts, and another for the email addresses associated to the email) than if I were to push all the data into the messages tables. I found this structure to be best, but I'm beginning to think it might be wrong and not best for performance. My question is, will it be in my best interests to restructure the database? Id rather not. I was thinking about moving the htmlbody and textbody and subject and date to the messages table. Another solution would be to grab all the emails and their data from the Parts table in one query. I could grab all the ids in one query and then do an IN(ids) for the second query. CREATE TABLE IF NOT EXISTS `messages` ( `id` int(10) NOT NULL AUTO_INCREMENT, `user_id` int(10) NOT NULL, `account_folder_id` int(10) NOT NULL, `hash` varchar(255) NOT NULL, `uid` int(10) NOT NULL, `seen` tinyint(1) NOT NULL, `flagged` tinyint(1) NOT NULL, `date_created` int(11) NOT NULL DEFAULT '0', `last_modified` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `hash` (`hash`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `message_parts_data` ( `id` int(10) NOT NULL AUTO_INCREMENT, `message_id` int(10) NOT NULL, `field_id` int(10) NOT NULL, `data` text NOT NULL, `date_created` int(11) NOT NULL DEFAULT '0', `last_modified` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `fields` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` text, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `fields` (`id`, `name`) VALUES (1, 'To'), (2, 'Subject'), (3, 'Date'), (4, 'From'), (5, 'CC'), (7, 'ReplyTo'), (8, 'textHtml'), (9, 'textPlain'), (11, 'Forward');
Thanks |
| Extract data to another computer Posted: 10 Jul 2013 12:10 PM PDT I have SQL Server 2008 R2 Express edition, and I want to export the data in that database to another computer so I can import it again in that computer. How please? What I have tried Right-click in the database -> Tasks -> Export -> Set server name + password + set database source. My problem I found that I have to set the destination, but I didn't find the destination as a file or something like that, I just found that the destination is the same database which I am extracting the data from. Help please, thanks in advance |
| disk I/O error in SQLite Posted: 10 Jul 2013 11:53 AM PDT What are the possible things that would trigger the "disk I/O error"? I've been having this problem and I couldn't find a solution. I have a SQLite3 database, and I'm trying to insert data from a file that contains SQL inserts. Sample data in the file: insert into files (filesize, filedate, md5, fullpath, origin) values (5795096,1370159412, "e846355215bbb9bf5f30102a49304ef1", "SDs/16G-1/DSC00144.JPG", "SDs"); insert into files (filesize, filedate, md5, fullpath, origin) values (5435597,1370159422, "1a7bcf3a4aaee3e8fdb304ab995ff80f", "SDs/16G-1/DSC00145.JPG", "SDs"); insert into files (filesize, filedate, md5, fullpath, origin) values (5121224,1370159432, "16d28e83599c731657a6cd7ff97a4903", "SDs/16G-1/DSC00146.JPG", "SDs");
I tried inserting that in the db file with the following command: $ sqlite3 allfiles.db < insert.sql
See below the error that I get: Error: near line 27: disk I/O error Error: near line 28: disk I/O error Error: near line 34: disk I/O error Error: near line 39: disk I/O error Error: near line 47: disk I/O error Error: near line 129: disk I/O error
The input lines that don't generate error are successfully included, but I don't understand why some lines have errors, and they are not inserted into the DB. There's nothing special in the lines with error, and if I run the command again I get errors in different lines, which means it's random (not related to the data itself). I tried adding pragma syncrhonous = off; and pragma temp_store = memory;, to no success. I'm running that on a lubuntu, which runs in a VirtualBox virtual machine. The host machine is a windows 7. The pwd of the files is a shared folder, i.e., it's a folder in the host machine. If I run it in a "local folder" in the guest machine, the error doesn't happen, although for some reason it's much slower... In any case, I'd like to know about the I/O error. |
| SSRS Reports on WUXGA Displays Posted: 10 Jul 2013 11:09 AM PDT We use WUXGA displays (1920 x 1200), and have lots of SSRS reports. Since these reports need to be printed once in a while the layout is adjusted to A4. However when the users look at the reports on the display around 40% of the screen is blank. Absolut waste of screen space. How can I adjust SSRS to use the full screen size for reports ? |
| how to build table relationships in a firebird database? Posted: 10 Jul 2013 02:53 PM PDT I am a beginner in Delphi and I have a Firebird database with 2 tables namely masterlist and daily collection. I used Zeos 7.0.3 to access my Firebird database. My masterlist contains the following columns: ╔══════╦══════╦═════════╦════════╗ ║ name ║ date ║ balance ║ status ║ ╚══════╩══════╩═════════╩════════╝
My daily collection contains the following columns: ╔══════╦══════╦═════════╦═════════╗ ║ date ║ name ║ payment ║ balance ║ ╚══════╩══════╩═════════╩═════════╝
I would like to build a relation in which the balance from masterlist will be copied to the balance column of the daily collection, and when I update the column in the daily collection it will also update the content of the masterlist. Hope this will be considered a good question I have tried very hard to make a useful question. |
| dead lock when updating Posted: 10 Jul 2013 01:53 PM PDT Update Operation.TrTable Set RecordId = RecordTABLE.newRecordId From Operation.TrTable tr Inner Join ( SELECT r.Id AS newRecordId, r.KeyM AS MappingKey From Operation.Record r WHERE r.KeyM > 0 ) RecordTABLE ON RecordTABLE.MappingKey = tr.KeyM WHERE tr.KeyM > 0 UPDATE Operation.Record SET KeyM = 0 WHERE KeyM > 0 UPDATE Operation.TrTable SET KeyM = 0 WHERE KeyM > 0
The error is: Transaction (Process ID 93) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. |
| How do I know if my database partitioning is done well? Posted: 10 Jul 2013 11:23 AM PDT I have a sneaking suspicion that the guy who wrote the partitioning functions at my place of work did a fairly poor job. For certain queries (maybe 20% of them) we see incredible performance boosts when using OPTION(LOOP JOIN). Isn't this bad? Shouldn't partitioning be done in a balanced way that really allows the optimizer to take over and properly optimize? I know the optimizer isn't always right, and I don't know much about partitioning, but I just feel that queries shouldn't require hints as often as we use them. Thoughts? We are currently running SQL Server 2008 R2. |
| unable to login oracle as sysdba Posted: 10 Jul 2013 07:53 PM PDT I just got 11gR2 running and was able to conn as sysdba. I shutdown and started up the database to mount a new pfile. Now, I cannot login as sysdba. My parameter for password file is: *.remote_login_passwordfile='EXCLUSIVE'
I am using sqlplus within the server. This is not a remote connection. [oracle@oel56 ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 5 22:50:46 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> conn / as sysdba ERROR: ORA-01031: insufficient privileges
Here's some more information: [oracle@oel56 ~]$ grep -E "ine SS_DBA|ine SS_OPER" $ORACLE_HOME/rdbms/lib/config.c #define SS_DBA_GRP "oracle" #define SS_OPER_GRP "oracle" [oracle@oel56 ~]$ id oracle uid=500(oracle) gid=500(oracle) groups=500(oracle),54321(oinstall),54322(dba),54323(oper) context=user_u:system_r:unconfined_t
|
| Oracle 10g Login Error Posted: 10 Jul 2013 11:42 AM PDT I have installed Oracle 10g on my pc. Now, when I try to login in with Scott/Tiger, system/manager or the Database username/password given while installing Oracle 10g, it gives me "ORA-12560 TNS Protocol Adaptor Error". I tried the solution mentioned here to start all Oracle Services and all the services are started, still I can't login. Please help. |
| Missing quotation - openquery to oracle Posted: 10 Jul 2013 12:53 PM PDT I am not able to successfully run a code with openquery to a oracle server. Please do not take into account field names/data types, as I had to present only a part of the whole procedure. It's quite long. I believe the problem lies in quotation marks, etc... The procedure compiles all right. Each time I execute the procedure, an error occurs: Msg 105, Level 15, State 1, Line 53 Unclosed quotation mark after the character string 'SELECT TO_NUMBER(XYZ_1) XYZ_1, XYZ_2, cast (''''0'''' as number(5)) as B1, cast(''''1753-01-01'''' as date) NULL_DATE
I am lost - where the heck should I put those missing quotation mark? CREATE TABLE #tmpXYZ Header ( [XYZ_1] [int] PRIMARY KEY, [XYZ_2] [varchar](20), [XYZ_3] [varchar](20), -- more fields [XYZ_N] [varchar](50) ) declare @sqlInv nvarchar(3000) set @sqlInv = 'insert into #tmpXYZ Header ( [XYZ_1], [XYZ_2], [XYZ_3], -- more fields [XYZ_N] ) select * FROM OPENQUERY(XYZ_ORACLE, ''SELECT TO_NUMBER(XYZ_1) XYZ_1, XYZ_2, cast (''''0'''' as number(5)) as B1, cast(''''1753-01-01'''' as date) NULL_DATE, -- more fields cast ('''' '''' as varchar(20)) as A19 from XYZ.V_HEADER where (DATE >= ''''TO_DATE(''''' + @startDate + ''''', ''''YYYYMMDD'''')'''' AND DATE <= ''''TO_DATE(''''' + @endDate + ''''', ''''YYYYMMDD'''')'''' AND QWE = ''''0'''' AND ABC = ''''13'''' ) '' ) ' exec sp_executesql @sqlInv
|
| SSIS Script to split string into columns Posted: 10 Jul 2013 11:01 AM PDT I have a dataset (log file) with a number of columns; one of them is "Other-Data" below (unordered string) and need to parse the string to create the derived columns according the u value (U1, U2, U3, etc...). The output columns should be something like: U1 U2 U3 U4 U5 etc. null odw odw : CH : de : hom null null EUR sss DE:de:hom null null EUR crm crm null null
Other-Data: u3=odw : CH : de : hom;u2=odw : Product : DSC-HX20V;~oref=http://www.bidl.ch/lang/de/product/dsc-h-series/dsc-hx20v u1=EUR;u2=sss:Checkout-Step4:Orderacknowledgement;u3=DE:de:hom;u11=1;u12=302338533;u13=SVE1511C5E;u14=575.67;~oref=https://shop.bidl.de/shop/bibit/success.do u15=1187;u13=SVE14A1C5E~VAIOEWY401;u11=1~1;u10=843.9~121.14;u9=1038~149;u3=crm : FI : fi : hom;u1=EUR;u2=crm : Checkout : Order acknowledgement;~oref=https://shop.bidl.fi/shop/bibit/success.do
Can anyone help with this? |
| MySQL said "InnoDB: Initializing buffer pool, size = 128.0M", does it mean it is using 128MB RAM? Posted: 10 Jul 2013 05:55 PM PDT I am just using a very few InnoDB tables (e.g. less than 1MB), but during MySQL startup, it said InnoDB: Initializing buffer pool, size = 128.0M
Does it mean even I am using in such a small size, the server still use 128M RAM? |
No comments:
Post a Comment