2
votes
2answers
110 views

How to get OUTPUT $action, Inserted.*,Deleted.* records in In SSIS

I was just wondering, in MERGE in T-SQL, you can output the OUTPUT $action, Inserted.* and Deleted.* records. I want to ask how can I get OUTPUT $action, Inserted.*, Deleted.* records in SSIS?
0
votes
1answer
45 views

SQL Server 2008 and constant expressions

If I have the following scenario, would Replace be evaluated a single time? DECLARE @text nvarchar(32) = 'This is a test field' SELECT textField FROM Table WHERE Id = @idICareAbout AND textField = ...
3
votes
1answer
28 views

How to only restore a specific filegroup?

I am using SQL Server 2008R2 (but I can upgrade if necessary) I have a database with multiple schemas and each schema is on a different filegroup (I wish I could just backup/restore a schema but that ...
0
votes
1answer
34 views

Restore Database with Multiple Restore commands

For automation, I have to dynamically construct the restore commands and thus I have created a script. A .bak file is created on a remote server and I receive it daily. The script builds the restore ...
-1
votes
1answer
59 views

How to aggregate related rows

I want to group these related rows into trips (where each trip consists of a sequence from point 1->2->3->2->1). route Date time ------------------------------- point1 15/10/2014 ...
1
vote
1answer
64 views

Unable to restore database

I am trying to restore several databases and I have no clue why some of them fail with the following error: Msg 3634, Level 16, State 1, Line 1 The operating system returned the error '1244(The ...
0
votes
1answer
48 views

Working out Duplicated Data in a Customer Data Base

I have a database in SQL2008 of customers, There is an ID number for the customer. What im trying to do is to create a NEWID field. We have duplicated customers that have different ID fields, I am ...
2
votes
1answer
40 views

Restore database with different filename

I have to restore a new back up file daily (as an agent job, after first deleting the existing db) but the file name will vary as it will include the timestamp as well. How can I achieve this so ...
1
vote
1answer
32 views

Minimum SQL Server rights that allow viewing column default values

I have an SQL Server 2008 database, some of the fields (columns) in some of the tables are configured with default values. I have a requirement to limit user security as much as possible, ideally ...
0
votes
0answers
50 views

Accidentally deleted records in MS SQL Server 2014

I have a very big problem with SQL Server data lost. Recently, i have performed SQL replication. and i found out that all exising records/rows in the subscriber was totally erased and replaced by the ...
-1
votes
1answer
35 views

Is there a way to copy/initialize values in a set

What I want to do (I know this does not work) is: update stats set BandwidthLastMonth = BandwidthThisMonth, BandwidthThisMonth=0 where myPk = 3 Is there a way to do this in a select ...
0
votes
2answers
32 views

Error with making backup in sql Server

We have a win server where installed sql server 2008. When I try to backup database, I have an error. Is anybody know what is the error? TITLE: Microsoft SQL Server Management Studio ...
0
votes
2answers
50 views

How to set host_name() value in a Sql Server Stored Procedure?

We have existing triggers which read the workstation id property of the connection string using host_name(). There are existing PowerBuilder client applications connecting to the database directly ...
2
votes
1answer
37 views

Create failed for Availability Group 'AGN'

I tried creating availability group (AOH) many times but no luck , It got failed every time. Please help me on this case. I configured Windows cluster (Name as 'WINCLUSTER') with two nodes. They are ...
0
votes
0answers
54 views

Prevent gaps while using Identity/sequence object in sql server

I have a table and I am trying to insert values in it. I want to prevent gaps in the primary key column. For primary column I have tried using Identity / sequence objects. I know that this is ...
0
votes
0answers
21 views

Exporting data using SBIDS

I have created lots of SQL reports using Microsoft Visual studio (from BIDS as part of the SQL package). I am running 2008R. When I export the data I would like to export to Access instead of the ...
0
votes
0answers
34 views

Measure fragmentation of a single index?

We are measuring the impact of different defrag approaches that we do one index at a time. How do we query to see fragmentation of a single index, by index name (or table + index). This is on SQL ...
1
vote
1answer
65 views

Dynamic Number of Columns in Pivot Table

I have a table to store StudentID, SubjectID, ObtainedMarks. Now I have to create a pivot table to show studentID and obtainedMarks in different subject. Number of subject may differ. I could generate ...
0
votes
0answers
5 views

Linked Server from SQL Server 2008 to Oracle not working

I have tried to make a linked server from SQL server 2008 to Oracle Server. I have successfully made a Linked Server from another machine to that oracle server but the one I'm making on my machine ...
2
votes
1answer
176 views

Why dropping all foreign keys from a script is not working? [closed]

I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no ...
0
votes
1answer
83 views

How to schedule jobs in Sql server 2008 for less than 10 sec?

I want to run a job every 3 seconds, however in SQL Server 2008 we cannot define an interval of less than 10 seconds. The job is used to insert/update visitor information, and segmentation ...
0
votes
2answers
57 views

Auditing the View Server State permissions on Server

We noticed that one of our servers has permissions granted to "all" users for "view server state". I am wondering if there is a way to audit the DMV's used by the user(s) on a regular basis as this ...
1
vote
0answers
39 views

What will do After Press F5? [closed]

select * from Employee; Name id Col1 Col2 Row1 1 11 1 Row2 2 12 3 Row3 3 13 30 Row4 4 14 44 Above is my table After PRESS F5 What process is running in Background ...
0
votes
1answer
49 views

How do I backup/restore database encryption key in order to restore on new server

I am currently in the process of restoring a TDE encrypted database to another instance. I have backed up master key and certificate, and have restored them to the new server fine, but I am still ...
0
votes
0answers
23 views

Import from one table in two tables where I need the auto-generated primary key in the second table

followning scenario: 3 Tables 1 Table name,street,country,bank This table needs to be imported to Table1 Address(Primary Key Autoincrement ID),name,street,country Table2 Address,bank So I need ...
0
votes
1answer
66 views

How can I reseed all identity columns?

I deleted all rows from 20 tables after disabling the constraints, triggers using the stored proc SP_MsForEachTable. Now before the import happens, I need to reseed all the identity columns back to ...
1
vote
1answer
72 views

SQL Server 2008 logging [closed]

We have a SQL server that has been hardening per government STIGs. Right now we are generating almost 10GB of Transaction log every day. (run sharepoint and some other applications). I'm trying to ...
2
votes
1answer
148 views

Find if Column Belongs to User Table

I want to compute the number of columns with not null constraint in my database. So I thought about querying the table sys.all_columns: select * from sys.all_columns The problem is that it ...
1
vote
1answer
147 views

View the order of triggers firing

We have 2 SQL Server 2008 environments with some triggers against a table containing business logic Within the 2 environments, we can see that the triggers are firing in a different order (thanks to ...
0
votes
2answers
21 views

CREATE DATABASE permission denied in database 'master'. unable to get the permission

I am making a database in sql server and it shows an error that "CREATE DATABASE permission denied in database 'master'" I am using the administrator log in itself. What i need to do? Please suggest a ...
1
vote
1answer
71 views

SQLDriverConnect fails when attempting to connect to Failover_Partner

SQLDriverConnect fails when attempting to connect to Failover_Partner. Connection string being used: Driver={SQL Native Client};Failover_Partner=tcp:dby;Server=tcp:dbx; ...
0
votes
1answer
64 views

deleting a table referenced by many foreign keys

I have a table that I want to drop and create again, because I want to add the IDENTITY property to a column, wich is not possible directly. The problem is that I can't drop it, because my column is ...
0
votes
0answers
65 views

Will SQL Server 2014 not cause problems with a SQL Server 2008 DB?

I will be doing development work on a database created in SQL Server 2005 that will be moved to SQL Server 2008. I have no problems moving them; I've done it before and it's always been painless ...
2
votes
3answers
301 views

How to pull rows only from the weekends?

My table data is like this: ID Name salary date 1 a 100 1/08/2014 2 b 200 2/08/2014 . . . . 26 z 2600 26/8/2014 Now I want to return only rows from Saturday or ...
1
vote
1answer
56 views

Cursors and concurrent users

I typically avoid cursor operations like the plaque, however, I have come across my first problem where using a cursor outperforms a query. So I am forced to use it. I have created an elaborate ...
4
votes
1answer
105 views

Why is SQL Server's online indexing causing blocking?

My understanding is that online indexing acquires locks at the beginning (Preparation) and end (Final) phases. However, we see a lot of DML queries blocked during the Build phase. A sampling of the ...
0
votes
1answer
52 views

Delete from table where not in another table using two columns

I am trying to get my head around this delete statement I am trying to accomplish. I understand how to delete from table where column not in ( subquery to table2) I have two tables with multiple ...
0
votes
1answer
91 views

How to generate replication snapshot without locking tables

When I reinitialize subscription and generate new snapshot it locks all tables and makes the database unusable for a very long time (never waited it out). Is there anyway to do this without locking?
0
votes
0answers
56 views

Subscription fails with Incorrect syntax near '9'

In my Distributor To Subscriber History I get the following error: Command attempted: 9 (Transaction sequence number: 0x0018E94F0000207A006200000000, Command ID: 59) Error messages: ...
1
vote
3answers
128 views

How to separate Stored Procedures (i.e. all the business logic) from Client Database so that it only contains client data?

Scenario: Our Application Database (in SQL Server 2012) contains entire business logic in the Stored Procedures (SPs). Everytime we have to publish the DB to the client, it unnecessarily results in ...
5
votes
2answers
359 views

Index seek much slower with OR condition compared with separate SELECTs

Based on these questions and the answers given: SQL 2008 Server - performance loss possibly connected with a very large table Large table with historical data allocates too much of SQL Server 2008 ...
0
votes
1answer
78 views

Update primary key column - NULL issue

I want to update a column that is a primary key using this code use BITS_TEE Update DMT_TEE_DTS_Production set ABCIndicatorCode = ( select HIST.ABCIndicatorCode from ...
1
vote
1answer
155 views

Change rowguidcol property to a different column

In my SQL Server 2008 database, I have a table with two uniqueidentifier columns. CREATE TABLE MyTable ( Col1 uniqueidentifier rowguidcol not null, Col2 uniqueidentifier not null, ...
1
vote
4answers
270 views

Large table with historical data allocates too much of SQL Server 2008 Std. memory - performance loss for other databases

Based on this question and the answers given: SQL 2008 Server - performance loss possibly connected with a very large table I have discovered memory allocation problem with SQL Server 2008. ...
7
votes
6answers
1k views

Estimate projected database growth

I recently started working with SQL Server 2008 as a DBA trainee. I need calculate the size of the database but also estimate its growth over recent months and the predicted growth for the next 12 ...
1
vote
2answers
206 views

SQL 2008 Server - performance loss possibly connected with a very large table

Migrated from http://serverfault.com/questions/615129/sql-2008-server-performance-loss-possibly-connected-with-a-very-large-table We have SQL Server 2008 with 3 active databases running on it. DB1 ...
0
votes
3answers
104 views

How to display posted on date and time like1 hr ago or 30 min ago

i want to show the posted date like 1 hr ago.. or 30 min ago.. declare @date datetime ='2014-07-24 11:50:22.033'; SELECT CASE WHEN DATEPART(DD,@date)=DATEPART(DD,GETDATE()) THEN CASE WHEN ...
0
votes
3answers
66 views

Matching two rows in a single table if two specific keys have different values

I have a table called settings in that there are two columns and data like following. settings value ------- ------ depots 12 projectid 156 client ...
1
vote
2answers
66 views

How to find usage of each object in other objects from a database?

I am with the task of taking an old SQL Server database and identify all of it's objects and their relationships, because the one who made it did not bother to use any foreign key, he just made all ...
1
vote
1answer
59 views

Single row to multiple rows with column combination

I have a table like this: ╔═════╦═══════╦════╦════╦═════╦═════╦═════╦═════╦════╦════╗ ║ ZIP ║ STATE ║ F ║ C ║ F1 ║ c1 ║ f2 ║ c2 ║ f3 ║ c3 ║ ...