0
votes
2answers
33 views

SQL Server 2008 R2 memory issue

I have read many posts regarding the memory but still its not clear. I have dedicated server SQL Server 2008 R2 with Windows 2008 R2, 32 GB RAM, 2 6 core processor and a database of around 60 GB on ...
0
votes
1answer
53 views

Long running simple query SQL Server 2008 R2

I have a very simple query running in a SQL Server 2008 R2 instance: SELECT TOP 10 * FROM messages m INNER JOIN recipients r ON m.id = r.id WHERE m.timeStamp > '2014-07-14T00:00:00.000' AND ...
0
votes
1answer
46 views

Database revision needed for sports events database

This is not a code review but more of a database schema review. I have a sports events website that I am doing in my spare time. I need assistance with the database design. I am about 80% completed. ...
0
votes
1answer
34 views

select multiple columns group by one with inner join

I have a problem with group by, i wanna select multiple column but group by only one column these below code which is i am used but its given a error. SELECT ...
2
votes
2answers
74 views

Way to do MERGE with update source when target not match?

I am trying to write a stored procedure that will take new values from a temp table, merge them into an actual table, giving me the ids of the inserted rows, and the rows that already exist matching ...
-5
votes
0answers
33 views

Microsoft SQL Server 2008 error 29 [on hold]

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and ...
0
votes
2answers
70 views

Query Optimization required - Subquery using Inner Join

Could anybody advise how can I optimize this query below please? SELECT IT.[iInternalTransactionID] , IT.[iInternalAccountID] , C.[iContractID] , IT.[dtTransactionDate] AS TransactionDate , ...
0
votes
1answer
33 views

Batch converting indexes to keys [closed]

I have a legacy MS SQL Server database where all the primary and foreign keys were created as indexes. I am developing the new version of our software with Entity Frameworks, which I have learned ...
1
vote
3answers
95 views

Preventing SQL Injection

I know that this is very dangerous: EXEC sp_executesql 'SELECT * FROM ' + @Table But, is this safe from SQL injection? IF EXISTS(SELECT * FROM information_schema.tables WHERE TABLE_NAME = @Table) ...
1
vote
0answers
16 views

Update Only Rows With Shared/Similar Value [migrated]

OK - I have a simple table - below - what I am trying to do is to identify only those rows that have a shared value - so anything that has a shared "apc" value - DO x else Do Y CREATE TABLE #test ...
0
votes
1answer
33 views

Sql Server sys.Traces log

Working with traces on production server I found some mysterious entries to me in sys.Traces, When I run below query it shows me status of all traces as ‘Paused’ along with entries in ...
1
vote
0answers
41 views

Database Structure Definiton

This is a question more so on the table definition vs actual programming. I have 6 sets of potentially 255 unique fields. I have a potential of getting 40 million records per year of these. For the ...
7
votes
3answers
155 views

Optimize select on subquery with COALESCE(…)

I have a large view that I use from within an application. I think I've narrowed down my performance problem, but I'm unsure how to fix it. A simplified version of the view looks like this: SELECT ...
0
votes
0answers
69 views

SQL Server Perfmon % Processor Time unbalanced [on hold]

I am somewhat of an accidental DBA, and I'm troubleshooting a SQL Server box where performance has been notoriously poor (as reported by the customer). I was not involved in the initial planning of ...
0
votes
0answers
32 views

Retrieve SPID For SSRS Request

Is it possible to determine what SPID was created to execute a SSRS report? I am trying to prevent users from running the same report multiple times. So what I was thinking of doing was retrieve the ...
1
vote
1answer
469 views

what is '#' in create table statement? [closed]

I recently saw a statement like: CREATE TABLE #tbl_AR_Data I wanted to know what is the purpose of the symbol # (NUMBER SIGN, hash, pound sign) in this statement?
0
votes
2answers
59 views

Are these linked server queries susceptible to SQL injection?

I'm using a stored procedure to generate a create table script on the fly based on parameters passed into it. I am validating that the server exists, and then validating if the table exists in the ...
1
vote
1answer
76 views

SQL Datetime / datetimeoffset(2) comparison & implicit cast wierdness

I discovered a problem when inserting data in our database. My insert statement was checking for the existence of data in the WHERE clause to prevent duplicate data being inserted. None was detected, ...
0
votes
2answers
49 views

Transfer all tables from a schema to other schema

I have two schema. Schema1 and Schema2 How would I transfer all the tables from schema1 to schema2?
3
votes
1answer
72 views

Sargable Queries

Very simple question I hope, I'm currently having some scripts I inherited into my project reviewed by the database admins. This has never been done before apparently and lots of issues are being ...
0
votes
1answer
32 views

Print the execution time on messages Pane?

How I can print the execution time of each query on messages Pane? For example the Select * from tbl1 runs for 10 mins then Select * from tbl2 runs for 15mins and so on.. Select * from tbl1 Go Select ...
-1
votes
1answer
32 views

Divide single column value into multiple column depending upon the bit value of another column [closed]

I have a table containing following fields. Id varchar(5), TranDate date, TranType bit, TranAmount decimal(15,3) My table looks like this: Id TranDate TranType TranAmount ...
1
vote
1answer
36 views

best possible index on this situation

create table #testing(id int, name varchar(50)) go Insert Into #testing Values(231, 'fasd') Insert Into #testing Values(232, 'dsffd') Insert Into #testing Values(233, 'xas') Insert Into #testing ...
-3
votes
1answer
56 views

Select Top N rows for Employees in a SQL Server table

I would like to select the TOP 2 activities carried out by all employees from an Employees table "Emp" which has a timestamp column. The regular "Select Top 2 * from Emp...." will only fetch the top ...
1
vote
1answer
34 views

set date value based on other row values

Sorry for the Header, I can't find the right way to describe what I want, possibly the reason why I can't get a solution by googling :) ... Here my problem: I've got a table with the following ...
0
votes
1answer
24 views

What are alternatives to Replication to distibute data (Microsoft SQL Server)

Is anyone knowledgeable of alternatives to SQL Server replication? I have a scenario where separate databases will share a lot of the same data which complex queries will match against. We want to ...
-3
votes
0answers
57 views

how to query to get data from multiple tables [closed]

i'm having data like this in two tables i want like this The query I'm using right now is: Select A.TaskCode,A.SubTaskCode,A.TagNo,A.TruckNo,A.Location ...
2
votes
1answer
53 views

MS SQL - export tables to format compatible with PostgreSQL

We're using MS SQL Server 2005 at work and I want to export our data to PostgreSQL for testing. MS SQL Server has an export function but the only one that looks like it would work would be to export ...
11
votes
1answer
289 views

Resolving deadlock from 2 tables only related through indexed view

I have a situation where I'm getting deadlocks, and I think I've narrowed down the culprits, but I'm not quite sure what I can do to fix it. This is on a production environment running SQL Server ...
0
votes
0answers
33 views

How to improve performance joining tables with no primary keys

Given two large tables of ~8,000,000 rows each. They are being joined on substrings of name address zipcode Naturally, the query is taking a long time. What are the best methods to improve it
1
vote
2answers
31 views

SQL view, how to append results from tableB with the results from tableA

I am using SQL server 2012. I would like to generate a sql view where I got all records from tableA. And below these results I would like to have the results from tableB. There are no relations ...
0
votes
1answer
31 views

SQL Server 2012 Express SP1 install still not 11.1

I run the SQL Server 2012 Express SP1 install and it succeeds. After I have rebooted my computer, I do a select @@version But still I only have version 11.0.21600.64. I have tried this on two ...
-1
votes
4answers
205 views

Database/Storage engine suggestion for a project involving heavy inserts?

I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which I need to monitor daily certain activities ...
1
vote
4answers
109 views

Is it ok to change from full recovery to simple recovery in Sql Server

I have an old database - a users membership/role that was setup automatically by an ASP.Net 2 application years ago: The Sql Server version currently running is: Sql Server 10.5.1617 The users ...
0
votes
1answer
33 views

Finding Item's distribution over customer routes

Please help me to find a solution. I have two tables [CustomerMaster and SalesData] as shown below. CustomerMaster | CODE | NAME | ROUTE | DIVISION | |------|------------|-------|----------| ...
0
votes
1answer
40 views

Join reorders results [duplicate]

Is there any particular reason why adding an additional join to an ordered table would reorder the result set? Using a left join on the existing result set table-valued function is similar to below ...
1
vote
0answers
61 views

Database Archival Process [closed]

I am designing a database archiving process - which is a automated process. Do you have any advice for how to accomplish these goals? Below are my thoughts : create another clone database ...
2
votes
1answer
55 views

Listener pointing to incorrect AlwaysOn Availability Group

All, Thanks in advance I have two instances of SQL Server 2012 SP1 on a three node WSFC with a HAdr group on each instance with a corresponding listener. Instance 1's listener points to Instance 1, ...
1
vote
1answer
32 views

SQL Server: affect other transactions?

I'd be surprised if this existed, but will ask just in case. Is there some mechanism which would allow one transaction to affect another (outside) transaction? For example: BEGIN TRY BEGIN TRAN ...
12
votes
1answer
133 views

“Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060” while altering the table

I am trying to alter a column in a table. The existing table is like this: CREATE TABLE [dbo].[table]( [id1] [int] NOT NULL, [id2] [int] NOT NULL, [id3] [int] NOT NULL, [name] ...
2
votes
1answer
65 views

SQL Server Replication questions

This is kind of the part 2 of Replicating a SQL SERVER database & Optimization that I started earlier this week, but I believe it owes to be a different questions now that I have a clear idea of ...
2
votes
1answer
68 views

Using scope_identity() to get ID “during” insertion of row?

I have a column that has an ident_current constraint on it to populate another column, like so: alter table dbo.Employee add constraint dbo_Employee_D1 default ident_current('dbo.Employee') for ...
4
votes
3answers
114 views

Virtual Log Files (VLFs) Fragmentation

I have a database with 160 VLFs. How do I determine whether the VLFs are fragmented? Further, how do I find the percentage of fragmentation? Another thing that I want to confirm: Are the following ...
7
votes
5answers
507 views

Performance tuning a Great Circle distance calculation

I have a query that I am using to find locations that are within 1km of a known point. To do this, I am using the Spherical Law of Cosines formula with my latitude and longitudes. Currently, the ...
0
votes
0answers
36 views

msdb 'backupset' log history

One of our production server‘s backup job is getting failed for one DB because of Low disk space when I looked into ‘physical_device_name’ column in ‘msdb.dbo.backupset’ to get the path of backups I ...
1
vote
1answer
35 views

Get data per Customer and per Date

How will I get the all the date even though there is no order between two dates? Sample Table OrderID | Date | CusID 33942 | 6-21-2014 | 6005 34059 | 6-20-2014 | 4003 53333 | ...
-2
votes
1answer
43 views

how to select column3 from rec1 value which is matched to column1 val in rec2

i am having following scenario : col1 col2 col3 10 2 13 13 4 14 14 5 15 15 6 16 form the above given table col3 value(13) in row1 is seen in col1 value(13) in ...
0
votes
1answer
45 views

Changing Databases Dynamically using Variables [closed]

I want change into a database programatically using in code: DECLARE @SQL nvarchar(2000) DECLARE @DBName sysname SET @DBName= 'foo' SET @SQL = 'USE [' + @DBName + ']' select @SQL execute @SQL ...
0
votes
0answers
39 views

How to aggregate similar columns inside a very long table? [duplicate]

I have quite a difficult problem that I don't have any idea how to approach it. I'll be glad to receive some advice. I have a very long table which contains details about a specific house residents. ...
0
votes
1answer
52 views

UPDATE not working properly with linked server

Please see a question I asked here earlier: http://stackoverflow.com/questions/24324823/update-not-working-properly-with-linked-server Dommer suggested using OPENQUERY and this does appear to resolve ...