1
vote
1answer
5k views

Nested with - Incorrect syntax near the keyword 'WITH'

With inside with This is ok WITH CS AS ( SELECT 'ab' as a ) SELECT * from cs http://data.stackexchange.com/mathematics/revision/138658/170321/comment-partition-by-question ...
0
votes
1answer
2k views

AdventureWorks2012: “The FILE SIZE property is incorrect” error while trying to attach sample database “Adventure Works for SQL Server 2012”

I am trying to attach the sample databases, for example, AdventureWorks2012_Data.mdf from codeplex "Adventure Works for SQL Server 2012" into a MS SQL Server 2012 (default) instances on a a virtual ...
0
votes
1answer
177 views

Multiple filters with joins

I just read this qestion: SQL Multiple Filter - Optimal Way I tried this with Northwind. I filter like this: declare @supplier int = 1 declare @category int = 1 select * from products p WHERE ...
2
votes
0answers
174 views

How to give permissions to 90+ SQL Servers without tweaking in each of them?

There are over 90+ MS SQL Servers (SQL Server 2005, 2008 and 2012). All in the same Windows domain (but physically all over the world). How to give a domain user (administrative, or other) ...
0
votes
1answer
127 views

How to reduce table duplication, while ensuring integrity of the database?

For example, in my app I have some class called Contact: case class Contact(name: String, email: String, phone: String) And several other classes have one-to-many relation to that contact (for ...
3
votes
1answer
4k views

How can I find and clean up unused database files?

How can I find and clean up unused (i.e. not used by any attached database) SQL Server database files (.mdf, .ldf, .ndf) that take up extra space on my server?
3
votes
1answer
2k views

query cache hit value is not changing in my database

The query cache hit value is not changing in my database only for some queries.If I write select * from drugs_info limit 1000; the query cache hit has changed, but if I execute the following ...
0
votes
2answers
4k views

Custom Auto-Generated Sequences with SQL Server [closed]

I have Employee table with Empolyee ID column EmpID (nvarchar(5)) , and I want to make EmpID's value to 00001 00002 00003 00004 .... .... How can I make Custom Auto-Generated ...
-1
votes
1answer
161 views

How to Recover corrupt SQL Sever database? [duplicate]

I am running Windows 2000 OS and while using my SQL application a message popped-up, can anyone figure it out, the message is like that. Msg 5172, Level 16, State 15, Line 1
2
votes
1answer
2k views

postgresql replication - pg_stat_replication is showing empty columns

I've a postgresql 9.2 streaming replication setup. It appears that the slave is getting the updates from master and is in sync. I've verified it by looking at pg_xlog dir and process list. $ ps aux ...
3
votes
1answer
208 views

Help with Cybernetics SAN Configuration

There are tons of articles out there recommending RAID/ LUN/ drive configurations for SQL Servers, but most leave me still questioning as they usually state "it depends" and for this reason I'd like ...
3
votes
1answer
2k views

Should snapshot agent continue to run in transactional replication?

We have transactional replication running for a large number of publications on sql server 2008 R2 2 node active/active cluster. I have noticed that the snapshot agent job runs hourly and it looks ...
2
votes
1answer
5k views

Error: Unable to write inside TEMP environment variable path

I am installing PostgreSQL 9.2.4-1-windows.exe on Windows 7 64 with Service Pack 1. I get the following, rather famous it seems, error: There has been an error. Unable to write inside TEMP ...
4
votes
1answer
323 views

filtered index null and > 0 disparity — need explanation

I'm getting behavior in a query plan I cannot explain. The difference is between two filtered indexes I'm testing with. One uses a where id is not null and the other uses where id > 0. In my ...
0
votes
1answer
642 views

SQL Server 2008 Convert String to Datetime question

I have a string column in a table that display data as "CXL P/D 08/15/13" I'm trying to convert this column to datetime, but I can't figure out how to extract only date and change the data type. ...
2
votes
1answer
1k views

How can I find out which tables use reference partitioning from the Oracle data dictionary?

I'm writing a generic drop-all-objects script for our Oracle databases. It generates drop table TABLE_NAME cascade constraints purge lines for all tables, amongst other object types, by looping ...
-1
votes
1answer
40 views

Would This Work? [closed]

This is just a basic script for updating my database from form data. I don't have much experience with databases and this might be a bad question but I have read online that you should use UPDATE ...
2
votes
1answer
1k views

SQL Server 2012 Page Compression - Determine compression ratio?

We're using SQL Server 2012 and have page compression turned on for a number of our larger tables. Is there a way we can determine the compression ratio we're getting? We'd like to be able to ...
1
vote
1answer
3k views

Allow self-referential foreign keys to be null from an alter-table

I am trying to establish a hierarchical relationship between rows in an existing table. There are two fields of interest: a not-nullable primary key field id, and a second nullable field parentId. ...
0
votes
2answers
3k views

how to join table from different database with different Users of database?

I have Two database called. Database1: host: 127.0.0.1 user: root1 pass: root1 dbname: db1 Table 1: Category ID INT(11) Primary Key Name VARCHAR(255) Database2: host: 127.0.0.2 ...
0
votes
2answers
55 views

What should we call a table that stores changes to another table? [closed]

At the moment, we have a Persons Table. But we also need another table to store changes made to that Persons Table. But, my question is, what naming convention is used for tables that store changes ...
3
votes
2answers
15k views

What is difference between primary index, secondary index, unique index and normal index?

I work on Oracle systems where I two type of indices exists in any table: Unique index (Based on any search key which is unique key). Normal index (Index made on any search key which is not ...
1
vote
1answer
161 views

Database Tuning Advisor Index Recommendations

How does DTA make an index recommendation? What is the technology behind that? If we can use same on our side then we can shorten the whole process of Tuning recommendation: creating trace file ...
3
votes
3answers
25k views

How to automatically execute a stored procedure every day at a particular time?

Can you please help me how to execute a stored procedure automatically in SQL database without any transaction in SQL? I want to execute on particular fix time.
2
votes
3answers
547 views

Oracle Equivalent of PostgreSQL CLUSTER

PostgreSQL has the CLUSTER command to group rows physically on disk. By grouping on information where "neighboring" rows (for lack of a better term) are often accessed together, performance improves ...
0
votes
1answer
111 views

What does maintenanceMode -7 mean on a mongo primary?

I've recently promoted a new member to primary of a mongo replicaset. Things seem to be running smoothly for several days and am not seeing any application symptoms of any problems. However peeking ...
0
votes
1answer
362 views

Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

I have a table of around 350m rows on a linked server in which I added an additional INT column to serve as a count(external_identification) of records as the result of a join on PACKAGE and DOC2. ...
0
votes
1answer
642 views

Index for ORDER BY on aggregate in MySQL

I'm working with the following query: SELECT *, SUM(entries_count) AS entries_count FROM leads WHERE promo_id IN (1, 2, 3) GROUP BY user_id ORDER BY entries_count DESC LIMIT 25 OFFSET 100; It's ...
3
votes
2answers
1k views

Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?

I've been growing concerned about the large size of ibdata1 that can never shrink even when using file-per-table on innodb Moving the undo log files outside seemed logical but this procedure seems ...
1
vote
1answer
527 views

one node in cluster got crashed ,how to recover it?

we are using percona galera cluster with 3 nodes. In my cluster one node got crashed due to low disk space ,we have cleared some log files and provided sufficient disk space.Now we are trying to start ...
1
vote
1answer
389 views

Paging and grouping using CTE?

Given the following code, from the answer to this question, how can I adapt it for paging? I've read that Common Table Expression is a good way to do paging, but since I implemented CTE to solve a ...
2
votes
2answers
568 views

Dynamic Joining Across Databases

I have a multi-database architecture with a single database for each client and a separate database to handle system-wide data. In this separate database, I have an 'Updates' table where a row is ...
0
votes
1answer
10k views

RegEx with update query my sql

i need to use a mysql update query with regex so my example is update table_name set coulmn_name = replace(coulmn_name, 'regex', ''); i will explain a specified thing i need to do. my column is ...
4
votes
1answer
5k views

Can I reset the ibdata1 and ib_logfile files when innodb_file_per_table is already in place

I am running into an issue where sensitive data was not encrypted properly at the application level of our production system. Even though the guilty records have been removed or corrected, scanning ...
1
vote
0answers
100 views

Displaying SSIS variabe values in Integration Services Dashboard

I have an SSIS package that executes stored procedures which return output parameters into ssis variables. How can I easily display these variables content in the Integration Services Dashboard ? ...
0
votes
1answer
265 views

Partitioning table with billion row

I have a big table referenced by other tables and need to split the table on several parts. I create table by CREATE TABLE product_part0 () INHERITS (product); Move some rows to product_part0 by ...
2
votes
0answers
1k views

Mysql slow writes

i have a strange problem with mysql. My selects goes fast. My insert/create/delete/drop/update go very slow. I have done some tests on 2 machines. My old machine and my new machine. Old is worst ...
4
votes
2answers
215 views

How can I integrate a new system to a design scheme I previously created?

This is a follow-up to a question I wrote a while ago, for reference, you can look it up here. I've developed a curriculum based database scheme that looks like this(Thanks Joel Brown) Now that a ...
2
votes
2answers
524 views

Scheduling duplication of two tables across instances

In my SQL Server 2012 database I have two tables: [tbl_Customers] - about 30 rows [tbl_Users] - about 10,000 rows I need to schedule the two tables above to be copied to a new instance, say every ...
3
votes
1answer
574 views

SQL Server WHERE clause on CLR method (spatial) peformance

I have a query that joins to a table containing 2 million rows and need to filter on its geometry column by its geometry type (using geom.STGeometryType()). The query takes less than a second to run ...
1
vote
0answers
616 views

kerberos authentication in sybase

we are setting up Kerberos authentication in Sybase (linux). DB version is 15.7. We are using Active directory for kerberos. We have done the configuration settings on the DB side and kerberos is ...
3
votes
2answers
1k views

Excessive replication snapshot size causing failure

I am searching for an explanation of a replication snapshot being in excess of 5 times what we're predicting Following a replication failure we are attempting to reinitialise subscriptions with a new ...
2
votes
1answer
83 views

Fixing a MySQL query that miscounts cases where count is 0

I have the following SQL code: SELECT widgets.campaign_name, widgets.enddate, widgets.active, widgets.id_widget,COUNT(*) AS entries FROM widgets LEFT JOIN entries ON widgets.id_widget = ...
-1
votes
1answer
1k views

Importing only one user data from a Dump file

I am using oracle 11gr1 SingleByte Database, I am trying to import a Dump file. but strangely the dump file contains multiple user objects(i.e, Import(IMPDP) is not done properly). example: . . ...
1
vote
1answer
435 views

How to UPDATE a table for the level of parents in a column?

I have a table with internal parents as id parent parent_level 1 NULL 1 2 1 2 3 1 2 4 3 3 where parent has a FK to id. How can I update the table ...
0
votes
2answers
671 views

What am I doing wrong in mysql database?

I'm creating a trigger that deletes rows from a table when they are inserted. However, when the trigger executes I get the following message: MYSQL ERROR: Can't update table 'player_viplist' in ...
2
votes
2answers
102 views

Several stays: many-to-many relationship

Could you please help me with the following problem? I'm a total novice in DB design. I have a table with researchers who visit our university. They come for some period of time (from, till). Now, ...
0
votes
0answers
948 views

FOR loop oracle query with calulation

I have a list of distinct areas, and would like to track statistics for those districts in a SQL statement. I currently have a setup that runs some summary information, but it doesn't include the ...
3
votes
1answer
5k views

Concatenate one-to-many field in single result?

Say I have the following query: SELECT * FROM AppDetails, AppTags WHERE AppDetails.AppID = '1' AND AppDetails.AppID = AppTags.AppID Which gives the following results: AppID AppName ...
1
vote
0answers
506 views

How to increase MySQL performance with joins

I have a MySQL database with InnoDB engine. Particularly one of my queries became very slow. The query is shown below: SELECT t1.`ARTREF1` FROM `lijnen` AS t3 INNER JOIN (`kortingen` AS t2, ...

15 30 50 per page