1
vote
1answer
4k views

Some queries are very slow after index rebuild

After finding out almost all databases on my SQL Server had fragmentation over 40%, I decided to do an index rebuild on all tables using a fill factor of 80. After rebuilding all indexes, some ...
1
vote
2answers
3k views

How can I keep a result set without keeping the transaction open?

The following documentation describes how to see the refcursor returned from a function, here, like this: CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT ...
1
vote
1answer
243 views

Can I force MSDTC to control only within a single database?

Using MS SQL Server 2008 R2 we have .NET code and we would like to use nested connections to the same database within the same transaction scope. I understand and accept that this will require ...
1
vote
2answers
549 views

Resume log shipping from a specific transaction

Is it possible to force SQL Log Shipping to run from a given .trn file onward? I have two databases, where one ships to another this was working fine. However I had to restore the primary database ...
0
votes
1answer
59 views

SQL Server 2012 Intellisense Support for Extended Events

Using SQL Server 2012 with SP1 for some reason when try to use extended events programmatically using T-SQL. I am not able to see intellisense working? Just trying to check is this something on my end ...
0
votes
1answer
315 views

Dimensional model or Relational model for data warehouse? [closed]

I have a pretty trivial questions in data warehouse, I know we use Dimensional data model to construct data marts and use Relational data model in staging area. What data model technique we use to ...
1
vote
2answers
2k views

Delete rows from a large table with joins and aggregating (resulting in 1 % less rows in the big table)

I got a bit stuck on performance, so I thought about asking a bit of help. I have a working query, but unfortunatelly it feels rather slow and I know it's not as performant as it could be made ...
1
vote
1answer
841 views

Why DBCC CHECKDB is multiple times in error log

I have an issue with dbcc checkdb command . In the errorlog file I find the dbcc checkdb command multiple time within the interval of 30-45 minutes. I have scheduled a job which grep keyword error ...
0
votes
2answers
228 views

Which is better? A join or a SELECT IN? [closed]

I have a set of ids that I extract using a join of 2 tables. Now I need some information from 2 other tables. What would normally be the best approach? So a SELECT IN using the set of ids I already ...
0
votes
2answers
202 views

Cycle or something similar in PL/SQL

Quite often it is necessary to do some repetetive operations / few line queries against set of objects. Let's say you have a list of accounts to unlock via alter user %username% account unlock and ...
0
votes
1answer
568 views

how to download oracle database to a remote server

I am trying to install Oracle 11g on my client's server. In order to download it to the server, I have followed this : Logged in to www.oracle.com with my client's username/pass from my desktop. ...
3
votes
3answers
51k views

Oracle password has expired, however I have no way of changing it

When I connect to my local development database, I get an error saying the password has expired. I've been trying to change it with sqlplus to no avail: C:\>sqlplus ...
1
vote
1answer
1k views

How to update where subquery value is null in SQL Server

I am attempting to update a column if another value from a subquery is less than a certain value or null: UPDATE Customer SET PriceClassID = 'A' FROM (SELECT custid, SUM(curybaldue) as last_sum FROM ...
0
votes
2answers
981 views

How to remove orphaned Log Reader Agents from Replication Monitor?

How do I remove the two Log Reader Agents with errors from the Replication Monitor? As you can see, the job does not exist (Error message produced by right click > Properties on errored Log Agent ...
1
vote
1answer
254 views

User switching accounts, can't log into MS SQL Server anymore [closed]

First a bit of background, for good or bad, we don't keep one user account attached to one person. The user accounts are matched to their position. For instance the CEO would have a userid of CEO. ...
0
votes
1answer
419 views

In Extended Events what is the difference between rpc_completed and sql_batch_completed? [duplicate]

I understand that rpc_completed is defined as the completion of a remote procedure call and sql_batch_completed is the completion of a t-sql batch. However can anyone explain in concrete terms what ...
4
votes
1answer
3k views

Licensing standalone instance of SSIS

Basic background: I have an application on an app server (no MSSQL installed) that I want to allow to call an SSIS package. One Solution: One option seems to be that we can install just SSIS on the ...
1
vote
1answer
1k views

Expdp error UDE-22303

I am experiencing the following error, when I am trying to make an export to the database using expdmp utility: UDE-22303: operation generated ORACLE error 22303 OCI-22303: type ...
10
votes
3answers
7k views

Can I create a user defined table type and use it in the same transaction?

When I execute the following (in management studio, GO will separate the commands into batches) use tempdb begin tran go CREATE TYPE dbo.IntIntSet AS TABLE( Value0 Int NOT NULL, Value1 Int ...
0
votes
0answers
356 views

For creating a quiz database, can I start with XML?

I'm trying to create a database consisting of quiz questions with multiple choice answers. The questions are expected to have the following components: a question 4 or 5 multiple choice ...
0
votes
1answer
429 views

Does my ERD account for my business logic

As part of my new and upcoming project, part of the business logic involves sending each and every one of my customers (assume they number in their 1000s) multiple (possibly customized) emails. Each ...
0
votes
1answer
644 views

oracle XE on windows XP stopped working

I know this will be greeted with derision, but yes I'm running Oracle XE on Windows XP. Long story involving office policies and VMs. Actually it's been working fine for a couple of years, but today ...
3
votes
1answer
170 views

Advice on setting up a Reporting server

I'd like to ask advice on setting up a reporting server please. We have a SQL Server (on a VM) that handles operations. (SQLOps) This primary SQL server instance has a secondary Log Shipping ...
0
votes
1answer
122 views

Database design [closed]

I have tables college and department. In college, I have serial-number and college-names, In department, i have nos, college-names and department-name. My challenge is that having a repeated ...
0
votes
0answers
65 views

joining two tables and counting joined rows

table 1 : categories -------------------------------------------------------------- cat_id | cat_title -------------------------------------------------------------- 1 | sports 2 | news ...
0
votes
0answers
97 views

Is there a syntax to access the table name in the select query?

In MYSQL. I want to select the records which has the same contents with the table name. for example, consider the following table. <TABLE : tbl_1> +-------+------+ | id | no | ...
0
votes
0answers
93 views

Optimizing group by <date> div <const> queries (MySQL)

I have a query that produces graph data from SQL. The div is to group the numbers into fix dates so it will draw a nice graph (the const is determined beforehand to try and divide the data into ~100 ...
11
votes
1answer
333 views

Should NOT IN be avoided?

Among SQL Server developers, it's a widely held belief that NOT IN is terribly slow, and queries should be rewritten so that they return the same result but do not use the "evil" keywords. (Example) ...
1
vote
1answer
323 views

MySql - is possible that performance issues are caused by dynamic SQL?

We have this MySQL SP, which uses dynamic SQL. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it uses dynamic SQL? Can dynamic SQL cause ...
1
vote
2answers
597 views

Oracle - Is it possible to maintain transaction across multiple schemas?

I got this database in oracle which includes 4 schemas. Each schema represents a logical layer. Each transaction (Stored Procedure) in the upper layer breaks into a couple of transactions in the lower ...
0
votes
2answers
424 views

Binary Log Format in MySQL

Reference manual for MySQL 5.6 states that " Some changes, however, still use the statement-based format. Examples include all DDL (data definition language) statements such as CREATE TABLE, ALTER ...
2
votes
1answer
122 views

Method to keep track of large number of sets with high cardinality

We're building an analytics service. One of our needs is to maintain ~100MM sets each with anywhere between one member to 1MM members, with each member represented as a long. We need to be able to ...
1
vote
1answer
306 views

.NET error when trying to create snapshot for transactional Replication

I encountered a bizarre prob today while creating a transactional publication on a SQL 2012 server running on Windows Server 2008R2. I had just had some success creating and initializing some Merge ...
2
votes
1answer
107 views

How is the number of records on a select determined as high or low by optimizer?

What is the threshold between using the index and doing an direct access to the database? I assume this decision is related to the number of index access (and subsequent access to the tables). If this ...
3
votes
1answer
636 views

SnapManager point in time restore problem

When using a clone-backup command in SnapManager (SMSQL), we are not able to restore the most recent logs, because they are not included in the snapshot. Does anyone have any ideas to help get those ...
2
votes
2answers
6k views

How do I install postgresql in Ubuntu?

I'm following these instructions, however I can only get to step 17.2. Despite installing postgresql successfully via the sudo apt-get install postgresql command, upon running initdb -D ...
4
votes
2answers
341 views

Make “as” mandatory when create column alias?

We found a lot of bugs are caused by missing comma, select aaaa, bbbb -- forgot comma ccc, dddddddd, ..... Is it a way to make expression name invalid and it must be expression as ...
2
votes
2answers
443 views

What can be done to further enhance performance of Multiple Join and Aggregate Queries?

I have a typical star schema simulated here, and I am mentioning two queries: first query simply joins the fact table with 2 dimension tables and 1 calendar table, and the second query joins and ...
1
vote
1answer
334 views

rman cloning rman-20207 error

I am trying to clone a 9i database (C) from a target 9i database (T). On running the rman script, i get the rman-20207 error : UNTIL TIME is before RESETLOGS time. When i do a list backup of ...
1
vote
1answer
79 views

Mysql with index [closed]

I have this following sentence: SELECT Day(C.DataCadastro) AS Dia, Month(C.DataCadastro) AS Mes, Year(C.DataCadastro) AS Ano, COUNT(I.Plano) AS Total FROM clientes AS C ...
2
votes
1answer
4k views

Why does SQL Server fail to create a procedure referencing a linked server?

Red Gate Deployment Manager (RGDM) today failed with a "login failed" error even though it had administrative control of the target server: 2013-11-04 17:13:22 +00:00 INFO Creating ...
0
votes
1answer
397 views

What performance should I expect from MySQL (Using Amazon RDS)

I'm trying to understand whether I'm doing something wrong, or expecting too much. I have an InnoDB table on a db.m1.large RDS (MySQL 5.6.13. 7.5 GB RAM) with ~1M rows. The primary key is a ...
0
votes
1answer
25 views

Basic metric information for views

I would like to output some basic metric information about my MySQL database. Such as the names and number of rows for all tables and views in a given database. Tables are no problem as I simply ...
2
votes
1answer
3k views

Automatic conversion of anonymous records returned from an UDF to well known table-type

Proprietary code (that we cannot change) has a bunch of user defined functions of the type: create or replace function f() returns record as $$ ... $$ which we call in the following way (for ...
6
votes
3answers
18k views

What permissions are necessary for truncating a table?

I have a sql account with the following permissions on a database: The db_executor role you see this account being a member of was created by this script: CREATE ROLE [db_executor] AUTHORIZATION ...
-2
votes
1answer
465 views

Example creating a PostgreSQL XML data type

I am looking for an SQL example where a table is created in PostgreSQL which has a field with data type XML.
2
votes
2answers
8k views

Insert values from a record variable into a table

I am developing a user-defined function that takes two arguments: create or replace function gesio( events_table_in regclass, events_table_out regclass) returns void as $$ ... $$ ...
0
votes
1answer
149 views

Can MySql nested SP be a bottleneck?

We have this MySQL SP, which calls a nested SP. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it calls a nested SP and uses temporary ...
0
votes
1answer
236 views

Optimizing multiple MySQL sub-queries and joins for multiple runs

The query (below) that's running is taking ~0.6 sec which normally would be fine but it gets run almost 1300 times for different custids. How can I make it run faster? Setup: Mac OS X 10.7.5 MySQL ...
0
votes
0answers
198 views

How much RAM do I need?

Let's assume a query like the following as typical: select A.`date` as `date`, count(*) as N from A as A join B as B on B.id = A.b_id join C as C on C.id = B.c_id join D as D on D.id = ...

15 30 50 per page