An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

1
vote
1answer
1k views

Heterogeneous queries error using stored procedure

Stored procedure ALTER PROCEDURE Proc_DayShift AS insert into #Punching_History SELECT * FROM SERVER2.MARS.DBO.TABLE1 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO SET ANSI_WARNINGS OFF ...
1
vote
1answer
306 views

how to transfer data from table to table

I have two tables Table1, Table2. From Table1 want to transfer data to Table2. Suppose I have the following query: Select * from Table1 where UserID between 5 and 10 I want to store above ...
4
votes
2answers
977 views

How can I speed up a query that orders by a calculated field?

I will try and give an example - this is not my table structure - I'm simply trying to outline the issue in order to find a solution... Person Id, Name BrothersNames Id, Name SistersNames Id, Name ...
6
votes
3answers
5k views

Executing Stored Procedure that accesses another SQL instance

I apologize if this question repeats another already asked. I have searched for hours and have not found one that fits my situation. Desired Outcome A user using SQL authentication has execute ...
2
votes
3answers
1k views

SQL Server Max number of Times a table can be referenced as a foreign key?

Is there any maximum on the number of times a single table can be referenced in a SQL Server database via foreign key? IE Table A A_ID Table B ~ B_ID, A_ID Table C ~ C_ID, A_ID Table D ~ D_ID, ...
7
votes
3answers
2k views

SQL Server - Multiple running totals

I've got a base table with transactions and I need to create a table with running totals. I need them to be per account and and also have a few running totals for each account (depending on the ...
1
vote
5answers
464 views

Frequent or Infrequent Commit

I'm trying to run an update query but still taking to long I have a commit in it. Is it better to set the commit to a higher value (5000) or lower (1)? I have to update 15 million records in a table ...
1
vote
1answer
500 views

Insert 4 tinyint values into varbinary(4)

I'm working on a function to convert the string representation of an IP into its varbinary(4/16) form. Right now, I have this: @stringIP = '192.168.0.3' select CAST(PARSENAME(@stringIP, 4) as ...
2
votes
1answer
157 views

Suggestions for SQL query repair tool or tips on building one?

Not sure if this is ontopic so I figure I would ask here and if its not liked then we can close it. Our hosting provider for a legacy application that I inherited is no longer supporting Oracle 10 ...
3
votes
1answer
500 views

Theta join explanation

Given the following question, I'd be glad if someone could help me. Take the following relational schemes: R(A, B , C) S(D, E, F) where A and D attributes are the primary keys. Assume to ...
0
votes
0answers
347 views

Oracle Flashback Archive aka Total Recall in MySQL

What would you suggest as a replacement system to achieve the same functionality of Oracle Flashback Archive aka Total Recall in other DBMS, in general or specifically in MySQL or MariaDB? ...
0
votes
1answer
340 views

Returning Multiple Result-Sets or Single XML Blods

I've been writing some new Stored Procedures, and find myself returning between 6-8 result-sets in a few cases. I've always worked under the assumption that there were certain inefficiencies with ...
4
votes
1answer
2k views

SSIS how to parse column with erratic data

I have a question on how to parse out a column and hoping a SSIS/Database guru can help out :) Here is the problem. I have a column saved as a string/varchar format in SQL Server, and need to parse ...
3
votes
2answers
4k views

Can someone explain why select with nolock will query a potion of updated data?

I was reading the answer from here (from stackoverflow, I think should ask in here) NOLOCK means placing no locks at all. Your query may returns portions of data as of before UPDATE and ...
1
vote
1answer
587 views

How to implement an effective ACL/auth in a relational db?

We are looking at implementing an ACL that fulfills the following requirements: Most users have access to an item (access to the item by default), but a few is denied access. Most users do not have ...
3
votes
1answer
3k views

Sql. Calculating the size in KB of each row

I'm building now a new sql 2005 database for my company, and we expect this database to be huge with 100's of millions of records. We already know we need to dedicate a separate server with new hard ...
0
votes
3answers
41 views

helping hand for sql server resources

I'm just getting into SQL-SERVER, so forgive me if this is a basic question. I need help, recently work on database programming, For my query development, to understand complex query, to can ...
1
vote
1answer
966 views

Recover PostgreSQL database data

I am using PostgreSQL in my project, Now by mistake I have deleted some records of one of my tables. Now is there any way to recover deleted Records in PostgreSQL?
1
vote
1answer
128 views

Is it possible to allow write-through computed columns for legacy code that expects a column to be read/write?

I have this existing table for IP storage: CREATE TABLE [dbo].[IPAddresses]( [ID] [int] IDENTITY(1,1) NOT NULL, [IPv4Address] [varchar](15) NULL, [IPv6Address] [varchar](45) NULL, ...
2
votes
2answers
464 views

SQL Joins Order of Operations

I'm new to SQL but happen to be dealing with a couple of rather larger table (on the order of a billion rows each). I want to do an inner join like the following: SELECT * FROM XTABLE X INNER ...
2
votes
1answer
1k views

Found important issue with SQL Server 2008 R2. Need Advice

Recently while installing my copy of SQL Server 2008 R2; the power went OFF. So now it does say the some features are installed and some are not but when I run SQL server detection manager it says ...
1
vote
2answers
5k views

Select multiple rows from one row based on column values

I have a product table where the value of two columns can imply either one or two actual product results. If column X = 1 AND Y = 0 return one result, if X = 0 and Y = 1 return one result, if X = 1 ...
4
votes
2answers
1k views

Query execution showing error as login failed

I want to retrive the table values from other server Query EXEC sp_addlinkedserver @server = 'OPSERVER', @srvproduct = 'SQL Server' EXEC sp_addlinkedsrvlogin @rmtsrvname = 'OPSERVER', @rmtuser = ...
1
vote
2answers
6k views

Exporting table records to an INSERT script

I'm setting up a test environment for development and need to export some static data from tables in Production. Ideally the exported product would take the form of a script with all the required ...
2
votes
2answers
1k views

Update column by comparing date and string values together

I have problem in updating table. I need to update table by comparing date and name with another table. I have two tables: table 1 : dim_sesid name(varchar) role ...
0
votes
1answer
308 views

Implementing Pub/Sub initiated from the database

I have a number of state-full Windows Services that currently poll the database looking for new or changed records in specific Tables and/or Views. For obvious reasons this limits scalability and is ...
0
votes
2answers
157 views

MOM and EOM in SQL Server 2005

What is the most sample and practical way to know the MOM (middle of month) and EOM (end of month) in an store procedure on SQL Server 2005? (without doing a select case / switch) EOM is the last day ...
2
votes
3answers
2k views

Easy or Schedulable way to move data from Production to Test

What I am looking to do, is schedule a copy of data from my production database to my dev/test database. The dev/test database will be newer than the production database in terms of schema, but the ...
2
votes
1answer
323 views

Is staggering reindexing jobs a good strategy?

I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes. ...
2
votes
1answer
487 views

high 'total_worker_time' for stored proc using OPENQUERY in SQL Server 2005

I have a stored procedure in SQL Server 2005 (SP2) which contains a single query like the following (simplified for clarity) SELECT * FROM OPENQUERY(MYODBC, 'SELECT * FROM MyTable WHERE Option = ...
1
vote
1answer
896 views

How do you monitor specific SQL statement of a specific user ?

We have a few standalone users here which cause high server loads for several 11.1.0.7 Oracle's. I spoke with them a few times but it seems that their queries are causing high loads and probably ...
1
vote
2answers
9k views

How to join two table and show one query result in MySQL?

I have a table1 (records 3), and table2 (records 3). Where i have field name in both. Now i want to make a result from those two table, which will show me both table records and take only one if there ...
2
votes
1answer
622 views

Oracle 11g production database copy to USB drive

What is the correct commands for backing up a production Oracle 11g database (while still running) to an external USB drive or different directory. I am new to Oracle and but have experience with SQL ...
3
votes
2answers
3k views

MySql: Delete all rows greater than n entries ordered by datetime

Can someone help me with a MySql query to delete all rows greater than n entries ordered by date? I.e. say I have 1200 rows of data with a timestamp column. I need to order it by date and preserve ...
10
votes
2answers
395 views

Decision criteria on when to use a non-dbo schema vs a new Database

I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out ...
5
votes
5answers
5k views

SQL Query Formatter

Are there any (Linux based) SQL Query Formatting programs/plugins/extensions? I use PostgreSQL and MySQL but other DB's are welcome as well. I can use a VM to test with but would prefer a Linux ...
4
votes
1answer
246 views

sp_sqlagent_get_perf_counters SQL Server 2005 running every 20 seconds

A little background info: We are running SQL Server 2005 SP2 on a server with Microsoft Windows Server 2003 SP2. When I run profiler I notice that EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters is ...
6
votes
2answers
2k views

How to retrieve foreign key constraints data

I'm looking for a query allowing to retrieve foreign key infos (each line: referrencing table & field, referrenced table & field) of an entire schema. I've found this, but does not gives all ...
3
votes
3answers
3k views

Disable trigger for just one table

Is it possible to disable a trigger momentarily but for just one table. For example I have table, TableA with an on insert, update and delete trigger. I also have a table B with the same triggers but ...
10
votes
1answer
6k views

Order by column should have index or not?

I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
2
votes
2answers
246 views

Sudden reduction in full backup size

I recently repaired a single corrupt page in one of the tables in a 70gb database. I had a backup which included the data contained in the page so I went about using DBCC CHECKDB with ...
1
vote
2answers
1k views

Backup of Postgres database

I want to create a backup of my database in Postgres. I'm using pgAdmin. on my database I have clicked right button of mouse after that I've selected the "Create a backup of database" After that ...
2
votes
1answer
132 views

strange optimization of query results in phantom value conversions

-- DB collation is German_PhoneBook_BIN CREATE TABLE [dbo].[partition_tbl]( [tbl_name] [varchar](255) NOT NULL, ) ON [PRIMARY] INSERT INTO partition_tbl VALUES ('some_table_{num}') INSERT INTO ...
2
votes
2answers
48 views

Size of the database and table while inserting and deleting in sql server 2005

I have created a table : Table1. Inserted 100 thousand rows and deleted. I did the same around 100 times ( inserting and deleting). Will it cause of increasing the database and table size every time? ...
7
votes
3answers
1k views

Oracle's left join and where clauses errors

CREATE TABLE "ATABLE1" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); CREATE TABLE "ATABLE2" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); ...
1
vote
0answers
61 views

What is the best way to find object dependancies in SQL Server [duplicate]

Possible Duplicate: How can I track database dependencies? How can I know which store procedure or trigger is using a table on SQL Server 2008 R2? I have been using the following SQL on ...
1
vote
1answer
116 views

Can a Database request automatically remove itself from the queue after a delay?

I'm using table locking in my Oracle database. I have a small program with a function that locks my table, does some things and unlocks it again. If many instances are run and requests are made ...
1
vote
0answers
442 views

time interval period in sql [closed]

How to display the time period of the most widely in use in ms sql server I have purchase data, such as: hours the number of purchases 10:00 2 12:00 4 13:00 5 14:00 1 ...
0
votes
2answers
2k views

Access ODBC reserved error 7711

I have user who is trying to access some tables from our Microsoft SQL Server 2008 R2 system (which very recently got moved to a brand new hardware/software stack) using Access 2007 via an ODBC ...
0
votes
1answer
250 views

FTS - Index rebuild causing CPU issues (SQL Server 2008 R2)

I am encountering some critical issues on SQL Server 2008 in a production environment; We have a database that contains a FTS index; we also have a maintenance job that rebuilds the FTS index and ...